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
9474
December 05, 2004 07:18AM
3307
December 09, 2004 09:08AM
3386
December 09, 2004 11:23AM
3011
December 09, 2004 07:38PM
3219
December 10, 2004 07:51AM
4282
December 10, 2004 08:42AM
2958
December 10, 2004 10:23AM
3362
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.