MySQL Forums
Forum List  »  Optimizer & Parser

Re: SELECT on multiple column index
Posted by: matthew crowley
Date: December 09, 2004 11:23AM

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.

Options: ReplyQuote

Written By
December 05, 2004 07:18AM
December 09, 2004 09:08AM
Re: SELECT on multiple column index
December 09, 2004 11:23AM
December 09, 2004 07:38PM
December 10, 2004 07:51AM
December 10, 2004 08:42AM
December 10, 2004 10:23AM
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.