This doesn't quite make sense. You seem to say that several rows will match
but then you say only one will. It must be one or the other. Perhaps I've
misunderstood you. You also seem to imply that with BETWEEN you get a full
table scan even though there is only one match for each row. That sounds
like an indexing problem, but it is hard to say without more information.
It would help us help you if you at least posted the query and the results
of EXPLAIN. It would probably also help if you told us more about the
tables, perhaps with SHOW CREATE TABLE.
Michael
Emmanuel van der Meulen wrote:
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
