Re: SELECT on multiple column index

[matthew crowley]

Not sure what you're looking for. Are you just trying to find rows where c1 > VALUE1 and c2 > VALUE2 ?

Can you provide more information about the problem you are trying to solve? there is probably another way to do it, and with only 70,000 rows it should return in 1 or two seconds max on decent hardware.

Andr� Schr�der wrote:
> 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.