> > I have a fairly large table (greater than 4mil rows) that I would to
preform
> > a query like:
> >
> > SELECT col1,col2 FROM table WHERE a=1, c=2 ORDER BY b DESC LIMIT 50;
> >
> > I have an index on the table INDEX1( a,b,c );
> >
> > When running the query as is, it takes around 4seconds. If I omit the
"DESC"
> > part the query runs in a fraction of a second.
> >
> > I would like the query to run faster when I use DESC. I looked at
> > myisamchk -R to sort by the 'b' index but I want to be sure it will
speed up
> > my query since it may take a while to sort all 4million rows.
>
> To improve things I'd suggest you drop the (a,b,c) index and create two
new ones:
> one on (a,c)
> and another on (b)
> and then do the myisamchk -R on the second index
> You might also consider the --sort-records=# option
The reason I decided to use (a,b,c) as the index is because I read in the
"How mysql uses indexes" (http://www.mysql.com/doc/M/y/MySQL_indexes.html)
that with the index (a,b,c) if you have a where clause where a=constant and
have order by b (the key_part2) it will use the index.
I may be wrong, but if I have an index just on (a,c) and a seperate index on
(b), the b index will span the whole table (greater than 4 mil rows) and
thus the cardinality is higher, while (a,b,c) would only span the rows that
I am looking for making the query faster. If this isnt right, I will go
ahead and try the other index method. The only reason I wouldnt just do it
is changing the indexes on the table takes quite a while.
What I forgot to include in my first post reguarding the optimization of the
order by, when the query contains "ORDER BY b DESC", explain says it will
use file sort (and hence the query takes around 4 seconds). When I drop the
"DESC" part from the ORDER BY clause, explain no longer says it will use
file sort and the query takes .1 seconds or so. I would like the DESC case
to be faster as that is the query I need to use. Is --sort-records the
option with myisamchk I want or --sort-index?
Thanks for the help.
ryan
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
