Re: Indexing: on WHERE columns or ORDER BY columns?
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.
Subject
Views
Written By
Posted
2404
May 31, 2005 04:58AM
1580
May 31, 2005 07:23PM
1553
June 01, 2005 12:29PM
1436
June 02, 2005 06:44PM
1546
June 01, 2005 11:41AM
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.