Hi list,
I have some trouble trying to optimise SELECT queries
that use ORDER BY condition.
I have found out that each time the server is busy
(100% CPU usage), it is due to queries that show
up in 'mysqladmin processlist' report with 'Sorting result'
state in 'Command' column.
I am trying to optimise it by adding proper indexes so
that these sorts could be avoided.
According to MySQL manual (12.4 How MySQL Uses Indexes),
SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3;
SELECT * FROM foo WHERE key_part1=const ORDER BY key_part2;
should use an index to avoid sorting
(second SELECT is actually a GROUP BY in the manual but I
assume it applies also to ORDER BY since the manual makes
no distinction between them).
In my case, sort is still used.
My table looks like:
CREATE TABLE pa (
region int(11) NOT NULL default '0',
code int(11) NOT NULL default '0',
pseudo varchar(20) NOT NULL default '',
date_depose int(11) NOT NULL default '0',
nom varchar(80) NOT NULL default '',
pays int(11) NOT NULL default '0',
commentaire text NOT NULL,
PRIMARY KEY (code),
UNIQUE KEY idx1(pseudo),
KEY date_depose(date_depose),
KEY pays_date_depose(pays,date_depose)
) TYPE=MyISAM;
and here are some EXPLAIN:
mysql> explain select code from pa order by date_depose desc;
+-------+------+---------------+------+---------+------+--------+----------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+------+---------+------+--------+----------------+
| pa | ALL | NULL | NULL | NULL | NULL | 151826 | Using filesort |
+-------+------+---------------+------+---------+------+--------+----------------+
1 row in set (0.00 sec)
mysql> explain select code from pa order by date_depose desc limit 2000;
+-------+-------+---------------+-------------+---------+------+--------+-------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+-------+---------------+-------------+---------+------+--------+-------+
| pa | index | NULL | date_depose | 4 | NULL | 151826 | |
+-------+-------+---------------+-------------+---------+------+--------+-------+
1 row in set (0.00 sec)
It seems that the index is used in second case only; may be because
of LIMIT option; but this is NOT what is described in the manual.
mysql> explain select code from pa where pays=2 order by date_depose desc;
+-------+------+------------------+------------------+---------+-------+------+----------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
| |
+-------+------+------------------+------------------+---------+-------+------+----------------------------+
| pa | ref | pays_date_depose | pays_date_depose | 4 | const | 1178 | where
|used; Using filesort |
+-------+------+------------------+------------------+---------+-------+------+----------------------------+
1 row in set (0.00 sec)
mysql> explain select code from pa where pays=2 order by date_depose desc limit 100;
+-------+------+------------------+------------------+---------+-------+------+----------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
| |
+-------+------+------------------+------------------+---------+-------+------+----------------------------+
| pa | ref | pays_date_depose | pays_date_depose | 4 | const | 1178 | where
|used; Using filesort |
+-------+------+------------------+------------------+---------+-------+------+----------------------------+
1 row in set (0.01 sec)
In this case, the index is NEVER used to avoid sorting even when LIMIT
option is restricting the result set.
I am currently running MySQL version 3.23.30-gamma, but I have
checked release notes for more recent versions I have not found
anything related to this.
What can I do to make these queries less CPU hungry ?
Thanks
--
Joseph Bueno
NetClub/Trader.com
---------------------------------------------------------------------
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
