Re: Stop query on first match

[Michael Stassen]

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
> 
>