RE: Stop query on first match

[Emmanuel van der Meulen]

On Monday, June 07, 2004 20:23, Justin Swanhart wrote

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

>I assume that you have multiple rows in table b that
>match the primary key on table a, but only one of
>those rows in b will be satisfied by your between
>condition of the query.

Hello Justin, fisrtly, thank you for your assistance.

Then, as I posted previously, keys do not match, however, a property on
table a matches only in respect of being between two properties on table b.

>First off, make sure that you have an index on table b
>that matches the primary key in table a.  Make sure
>the data types and lengths are the same for both keys.

Please see previous post.

> Secondly, make sure that your key cache is large
> enough  so that the keys to the tables are buffered.

I'm not sure it is this.

> If these assumptions are not correct, then you will
> need to post the query, the explain of the query, and
> the "show create table" for each table in the query so
> that we have a better idea what you are dealing with.

Please see previous post.

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

> Oracle's FIRST_ROWS simply tells the optimizer to
> prefer index scans over a FTS where lots of rows may
> be returned by the query.  It won't modify the
> behavior of a query in the way you want it to.

Thank you for info.

> The only way I can think to implement the behavior
> that you are looking for is to:
> 1) get all the records from "a" that you need
> 2) execute a second select for each row in "a" on "b"
> with your between condition and a LIMIT clause so that
> only one row is returned.

Until 4.1 when subselects become avaialable, I suspect this is a workaround.

> Still, if multiple rows
> exist in "b" that match the PK on "a", those rows will
> probably be scanned in filesort order, which means
> that multiple rows will be looked at unless your
> between matches the first row inserted, except if you
> have an appropriate index.

Please see previous post.

Justin, again, thank you for your asistance.

Kind regrds
Emmanuel