Hi All, I need advice for creating proper INDEX for a query.
The table is like: mysql> desc article; +----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | title | varchar(200) | NO | | | | | timestamp | int(11) | NO | | 0 | | | active | tinyint(4) | NO | MUL | 1 | | | body | mediumtext | NO | | | | | source | int(11) | NO | | 1 | | | category | int(11) | NO | | 0 | | +----------------+--------------+------+-----+---------+----------------+ mysql> select count(*) from article; +----------+ | count(*) | +----------+ | 536023 | +----------+ I have created few indexes for this table: mysql> show index from article; +---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | article | 0 | PRIMARY | 1 | id | A | NULL | NULL | NULL | | BTREE | | | article | 1 | index1 | 1 | active | A | NULL | NULL | NULL | | BTREE | | | article | 1 | index1 | 2 | timestamp | A | NULL | NULL | NULL | | BTREE | | | article | 1 | index1 | 3 | id | A | NULL | NULL | NULL | | BTREE | | | article | 1 | index2 | 1 | active | A | NULL | NULL | NULL | | BTREE | | | article | 1 | index2 | 2 | source | A | NULL | NULL | NULL | | BTREE | | | article | 1 | index2 | 3 | timestamp | A | NULL | NULL | NULL | | BTREE | | | article | 1 | index2 | 4 | id | A | NULL | NULL | NULL | | BTREE | | | article | 1 | index3 | 1 | active | A | NULL | NULL | NULL | | BTREE | | | article | 1 | index3 | 2 | source | A | NULL | NULL | NULL | | BTREE | | | article | 1 | index3 | 3 | category | A | NULL | NULL | NULL | | BTREE | | | article | 1 | index3 | 4 | timestamp | A | NULL | NULL | NULL | | BTREE | | | article | 1 | index3 | 5 | id | A | NULL | NULL | NULL | | BTREE | | +---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ The query is like: mysql> explain SELECT id, title, timestamp FROM article USE INDEX(index3) WHERE active = 1 AND source = 5 AND (category = 411 OR category = 547 or category = 559) AND timestamp > 0 ORDER BY timestamp DESC, id DESC LIMIT 0, 20; +----+-------------+---------+-------+---------------+------------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------+------------+---------+------+------+-----------------------------+ | 1 | SIMPLE | article | range | index3 | index3 | 13 | NULL | 1483 | Using where; Using filesort | +----+-------------+---------+-------+---------------+------------+---------+------+------+-----------------------------+ Question: does the query use correct index? I am asking because I noticed such queries were recorded in mysql long query log, it somehow took about 14 seconds (# Query_time: 14 Lock_time: 0 Rows_sent: 20 Rows_examined: 1483) while I expect none of query should take more than 2 seconds. I also noticed that, when the LIMIT offset is a big value, it is recorded too even the index is correct, for example. SELECT id, source, title, timestamp FROM article USE INDEX (index2) WHERE active = 1 AND source = 7 ORDER BY timestamp DESC, id DESC LIMIT 0, 20; usually only takes 1 second, but SELECT id, source, title, timestamp FROM article USE INDEX (index2) WHERE active = 1 AND source = 7 ORDER BY timestamp DESC, id DESC LIMIT 7600, 20; could be recorded as 14 seconds too. Any advice or suggestion are appreciated. Thanks.
