SELECT on multiple column index
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.
Subject
Views
Written By
Posted
SELECT on multiple column index
9971
December 05, 2004 07:18AM
3488
December 09, 2004 09:08AM
3693
December 09, 2004 11:23AM
3229
December 09, 2004 07:38PM
3520
December 10, 2004 07:51AM
4501
December 10, 2004 08:42AM
3176
December 10, 2004 10:23AM
3608
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.