SELECT on multiple column index

[Andr Schršder]

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.