MySQL Forums
Forum List  »  Performance

Re: Indexing: on WHERE columns or ORDER BY columns?
Posted by: Benoit St-Jean
Date: June 03, 2005 05:21PM

Craig Matthews wrote:
> Thanks Kim.
>
> With a LIMIT of 15, the query takes "15 rows in
> set (5.16 sec)"
>
> My sort_buffer_size = 16M
>
> After running the query for 5.16 seconds, I see
> the following:
>
> mysql> SHOW STATUS like 'sort%';
> +-------------------+-------------------+
> | Variable_name | Value |
> +-------------------+-------------------+
> | Sort_merge_passes | 0 |
> | Sort_range | 671 |
> | Sort_rows | 1222208 |
> | Sort_scan | 3324 |
> +-------------------+--------------------+
> 4 rows in set (0.00 sec)
>
>
> So it seems that the merge_passes is doing
> alright. What else can MySQL do to optimize an
> ORDER BY which is a very common command and
> absolutely necessary in situations? My test table
> is only 800,000 rows but the production table will
> have 2 million or so!

Well, removing the filesort isn't going to solve everything is your query matches 250000 rows out of 800000 ! What is the selectivity of col1 and col2 ? If col2 is more discriminant than col1, than having the where clause and the index reversed would help the SELECT part.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Indexing: on WHERE columns or ORDER BY columns?
1521
June 03, 2005 05:21PM


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.