On Tue, 1 May 2001, ryc wrote:
> > > 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.
This is correct, but that way the 'where a=1 AND c=3' clause would use the index only
for the a=1 condition and would do an exhaustive search to find which of those rows
also have c=3. This is why I suggested the (a,c) index as a better alternative for
your where clause.
The reason why I suggested an index on (b) by itself is for the soring of the results.
BUT I don't know whether mysql will utilize this index to sort the temporary results
or whether only one index can be used from the beginning till the end of a query.
Anyone a bit more experienced willing to lend a hand here?
If the second index is not used for the sorting, then since both your 'where'
conditions are using constants (a=1 and c=3), the optimal index for you is on (a,c,b).
cheers again,
thalis
---------------------------------------------------------------------
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
