In the last episode (Jan 06), Ludwig Pummer said:
> I'm trying to eliminate a filesort from a very simple query but
> having no luck. Even though I've read the manual section on when
> indexes are used in conjunction with ORDER BY, it seems I just can't
> get an index to be used.
Sometimes a filesort is truly the most efficient solution.
> The table:
> CREATE TABLE `minifatboy` (
> `p1date` date NOT NULL default '0000-00-00',
> `p2date` date NOT NULL default '0000-00-00',
> `struct` char(120) binary default NULL,
> PRIMARY KEY (`p2date`,`p1date`)
> ) TYPE=MyISAM
...
> SELECT struct FROM minifatboy ORDER BY p2date, p1date;
>
> An explain gives me:
> mysql> explain select struct from minifatboy order by p2date, p1date;
> +------------+------+---------------+------+---------+------+--------+----------------+
> | table | type | possible_keys | key | key_len | ref | rows | Extra
> |
> +------------+------+---------------+------+---------+------+--------+----------------+
> | minifatboy | ALL | NULL | NULL | NULL | NULL | 999370 | Using
> filesort |
> +------------+------+---------------+------+---------+------+--------+----------------+
> 1 row in set (0.00 sec)
>
> I feel like I have to be missing something obvious here. I don't want
> to have to wait while MySQL performs a filesort to sort the data into
> the order already specified by the primary key. It doesn't matter for
> minifatboy, but for fatboy this means performing a filesort on a 31gb
> table. Is it just because I'm not restricting rows and therefore
> MySQL thinks it should just do a table scan? I know I can try to
> force the use of an index with MySQL 4, but I'd rather not upgrade if
> I don't have to (USE INDEX doesn't help, btw).
Your two choices are: walk the primary index in order and then do
random disk seeks through the table to fetch the records, or do a
sequential pass through the entire table, sorting the data (either in
memory or in a tempfile).
A million random I/Os, assuming say 90% of them are cache hits (very
optimistic), on an average disk than can do 175 I/Os per second, would
take around 1000000*.10/175=571 seconds. A full table scan plus a
sort, assuming worst mergesort case of 2 sets of tempfiles, on a
million 128-byte records, on a slow disk that does 10MB/sec would take
around 1000000*128/(10*1024*1024)*5=61 seconds. If 'struct' was a
varchar with lots of blank padding, the table scan would go even
faster.
If your table was created in p2date, p1date order, then the random I/Os
would really be sequential and would go fast, but mysql doesn't know
the physical ordering of the records. I think USE INDEX should have
worked, actually.
If this is the most common query, and other queries also have WHERE
clauses involving p2date, you might want to test this as an InnoDB
table. They store table data inside the primary index, so it should
just have to walk the index to get 'struct' sorted correctly.
--
Dan Nelson
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
