MySQL Forums
Forum List  »  Optimizer & Parser

SELECT on multiple column index
Posted by: Andr Schršder
Date: December 05, 2004 07:18AM

I have a multiple column index over two (actually n) columns and wish to find the next higher combination.

What I thought I could do was (granted I have an index i0 over c1,c2):

SELECT c1,c2 from foo where (c1,c2)>(value1,value2) order by c1,c2 limit 1

This works fine but the performance is dead slow. EXPLAIN shows it won't use any indexes but does a full table scan. My table is quite big though (>70000 rows)

If I do this:

SELECT c1,c2 from foo where (c1=value1 and c2>value2) OR (c1>value1) order by c1,c2 limit 1

then the performance is much better. Problem is since I am doing this over n columns, I get HUGE queries that are pretty complex to handle.

Is this intentional or is maybe my complete approach totally wrong? I really just need the next higher combination and only one row to return.

Any answers are *REALLY* appreciated.

Options: ReplyQuote


Subject
Views
Written By
Posted
SELECT on multiple column index
9459
December 05, 2004 07:18AM
3302
December 09, 2004 09:08AM
3374
December 09, 2004 11:23AM
3000
December 09, 2004 07:38PM
3208
December 10, 2004 07:51AM
4270
December 10, 2004 08:42AM
2949
December 10, 2004 10:23AM
3353
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.