Hello all,
I see a similar question was asked before, but it was not answered. I hope
someone can assist me.
My query uses two tables. The query selects one row on table b for each row
on table a, but uses between in the select.
It can only ever return one row from table b, for each row on table a, due
to the contents that is stored in table b. The table contains in excess of
a million records. What happens as a result of the between is that for the
query, several rows seem to be candidates on table b, but once the query
evaluates and sifts through the candidate rows on table b, only one row will
ever match. So if I could inform MySql to stop the query for the particular
row, once one row on table b matches the row on table a, the query would
return hundreds of times faster. As an experiment I took one example and
used limit and the query reduced from 4 secs to .01 sec. However, when
doing the 'live' query, I cannot use limit because, I do not want overall
only 1 row returned, I want one row returned for each of the rows from table
a which has 1 match each on table b.
I've looked in several books and searched Google but cannot get a way of
doing this. It seems Oracle has a 'FIRST' in their select which they use
for such a use case. But I do not see anything for MySql anywhere.
Could someone please assist me.
Kind regards
Emmanuel
