MySQL Forums
Forum List  »  Optimizer & Parser

Re: SELECT on multiple column index
Posted by: Andr Schršder
Date: December 10, 2004 07:51AM

Neither of those will work because what I am looking for is:

The next entry with the same value for c1 but a higher value for c2 OR
the next entry with a higher value for c1 and the lowest possible value for c2.

If I did "WHERE c1>val1 and c2>val2" I would skip all the rows with "c1>val1 and 0<=c2<=val2" which is not what I do not intend to do.

If I do:

SELECT data WHERE (c1=val1 and c2>val2) OR (c1>val1) LIMIT 1

it uses the index and works fine BUT the server stalls for this query (affecting all other users) and the performance is very bad.

If I do this:
SELECT data WHERE (c1=val1 and c2>val2) ORDER BY c1,c2 LIMIT 1;

then, if I find no result, I do:

SELECT data WHERE(c1>val1) ORDER BY c1,c2 LIMIT 1;

This is MUCH faster, although it is at least one query for every involved column. My queries are actually more complex since they involve up to 100 columns.

What gives me the exact same result but does not use the index, even if I "FORCE" it is:

SELECT data where (c1,c2)>(val1,val2) ORDER BY c1,c2 LIMIT 1;

This does not stall the server but it takes ages since it operates without index.

Options: ReplyQuote

Written By
December 05, 2004 07:18AM
December 09, 2004 09:08AM
December 09, 2004 11:23AM
December 09, 2004 07:38PM
Re: SELECT on multiple column index
December 10, 2004 07:51AM
December 10, 2004 08:42AM
December 10, 2004 10:23AM
December 10, 2004 11:02AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.