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!
