It's possible mysql optimizes queries incorrectly. I've had to "correct it" on a few occasions. If you look at the results of the
explain for Q1, you'll notice that in both queries the key_len is 1. Meaning Mysql thinks it only needs to look at the first
"character" of the index. Based on that "optmization", it sees that it will be selecting just about every record in the database, so
it doesn't matter what index it uses for searching.
By adding the timestamp clause, Mysql is actually supposing it will find only 64,448, which is not correct based on the result of
your count(*) query. Mysql decides to use a different index based on the 64K.
Notice that in the second query, Mysql isn't using a file sort. This means it's probably using the index to perform the sort. It's
likely it the sort that is taking longer in the first query, not the search. Try both without the order by clause.
As for Q2, the queries look the same to me. Perhaps you copied/pasted wrong? But as you'll notice, your Q2 has the same thing as Q1,
one is doing a file sort the other not. So again, it may be the sorting that is taking a long time.
You can run an analyze table to see if that fixes things. The summary numbers Mysql has for the indexes may be incorrect and that
will fix it. You can also do a "SHOW INDEX FROM table" to see your index structure. You would be interested in the cardinality
(uniqueness) column.
----- Original Message -----
From: "Kelvin Wu" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Saturday, January 13, 2007 11:04 PM
Subject: Index and Order By
I am looking for help and suggestion.
Mysql Ver 12.18 Distrib 4.0.12, for pc-linux (i686)
The table structure:
mysql> desc article;
+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| title | varchar(200) | | | | |
| timestamp | int(11) | | | 0 | |
| active | tinyint(4) | | MUL | 1 | |
| source | int(11) | | | 1 | |
| category | int(11) | | | 0 | |
+----------------+--------------+------+-----+---------+----------------+
I created multiple parts of Index for this table:
mysql> create index bynone on article (active, timestamp, id);
mysql> create index bysource on article (active, source, timestamp, id);
mysql> create index bycategory on article (active, source, category,
timestamp, id);
There are around 130000 rows in this table, indexes are created on the
existing rows and records keep growing.
And here are the questions:
Q1: the query is supposed to use index bynone but ...
mysql> explain select * from article where active = 1 order by timestamp
desc, id desc limit 0, 20;
+---------+------+----------------------------+----------+---------+-------+--------+-----------------------------+
| table | type | possible_keys | key | key_len | ref |
rows | Extra |
+---------+------+----------------------------+----------+---------+-------+--------+-----------------------------+
| article | ref | bynone,bysource,bycategory | bysource | 1 | const |
129805 | Using where; Using filesort |
+---------+------+----------------------------+----------+---------+-------+--------+-----------------------------+
wrong index key
mysql> explain select * from article where active = 1 and timestamp > 0
order by timestamp desc, id desc limit 0, 20;
+---------+------+----------------------------+--------+---------+-------+-------+-------------+
| table | type | possible_keys | key | key_len | ref |
rows | Extra |
+---------+------+----------------------------+--------+---------+-------+-------+-------------+
| article | ref | bynone,bysource,bycategory | bynone | 1 | const |
64448 | Using where |
+---------+------+----------------------------+--------+---------+-------+-------+-------------+
2nd query is fater than the first one, though i dont quite understand why
timestamp > 0 must be used here, and why rows are different?
mysql> select count(*) from article where active = 1 and timestamp > 0;
+----------+
| count(*) |
+----------+
| 129805 |
+----------+
Q2: the query is supposed to use index bysource but ...
mysql> explain select * from article where active = 1 and source = 1 and
timestamp > 0 order by timestamp desc, id desc limit 0, 20;
+---------+-------+----------------------------+----------+---------+------+-------+-------------+
| table | type | possible_keys | key | key_len | ref |
rows | Extra |
+---------+-------+----------------------------+----------+---------+------+-------+-------------+
| article | range | bynone,bysource,bycategory | bysource | 9 | NULL |
75925 | Using where |
+---------+-------+----------------------------+----------+---------+------+-------+-------------+
mysql> explain select * from article where active = 1 and source = 2 and
timestamp > 0 order by timestamp desc, id desc limit 0, 20;
+---------+------+----------------------------+------------+---------+-------------+------+-----------------------------+
| table | type | possible_keys | key | key_len |
ref | rows | Extra |
+---------+------+----------------------------+------------+---------+-------------+------+-----------------------------+
| article | ref | bynone,bysource,bycategory | bycategory | 5 |
const,const | 1110 | Using where; Using filesort |
+---------+------+----------------------------+------------+---------+-------------+------+-----------------------------+
mysql picked up wrong index key
mysql> explain select * from article where active = 1 and source = 3 and
timestamp > 0 order by timestamp desc, id desc limit 0, 20;
+---------+------+----------------------------+----------+---------+-------------+------+-------------+
| table | type | possible_keys | key | key_len |
ref | rows | Extra |
+---------+------+----------------------------+----------+---------+-------------+------+-------------+
| article | ref | bynone,bysource,bycategory | bysource | 5 |
const,const | 6445 | Using where |
+---------+------+----------------------------+----------+---------+-------------+------+-------------+
mysql> explain select * from article where active = 1 and source = 12 and
timestamp > 0 order by timestamp desc, id desc limit 0, 20;
+---------+------+----------------------------+------------+---------+-------------+------+-----------------------------+
| table | type | possible_keys | key | key_len |
ref | rows | Extra |
+---------+------+----------------------------+------------+---------+-------------+------+-----------------------------+
| article | ref | bynone,bysource,bycategory | bycategory | 5 |
const,const | 6108 | Using where; Using filesort |
+---------+------+----------------------------+------------+---------+-------------+------+-----------------------------+
mysql used wrong index key
Removing timestamp > 0, some work some wont.
mysql> explain select * from article where active = 1 and source = 12 order
by timestamp desc, id desc limit 0, 20;
+---------+------+----------------------------+----------+---------+-------------+------+-------------+
| table | type | possible_keys | key | key_len |
ref | rows | Extra |
+---------+------+----------------------------+----------+---------+-------------+------+-------------+
| article | ref | bynone,bysource,bycategory | bysource | 5 |
const,const | 5270 | Using where |
+---------+------+----------------------------+----------+---------+-------------+------+-------------+
Using of "timestamp > 0" caused using different index on different source =
xxx... why is like that so?
Q3: the query is supposed to use index bycategory and it seems work fine so
far...
mysql> explain select * from article where active = 1 and source = 1 and
category = 53 order by timestamp desc, id desc limit 0, 20;
+---------+------+----------------------------+------------+---------+-------------------+------+-------------+
| table | type | possible_keys | key | key_len |
ref | rows | Extra |
+---------+------+----------------------------+------------+---------+-------------------+------+-------------+
| article | ref | bynone,bysource,bycategory | bycategory | 9 |
const,const,const | 9619 | Using where |
+---------+------+----------------------------+------------+---------+-------------------+------+-------------+
Any idea?
Thanks a lot!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
