No advice? Anyway after monitoring slow queries for few days, I found most likely the following queries caused bad performance and locked table for long time:
........ LIMIT 16780, 20 A big offset! Even the index is properly used. After restricting offset value within the software, eg, return error directly when offset is more than 4000, this issue seems to disappear. On 2008-11-05, Kelvin Wu <[EMAIL PROTECTED]> wrote: > > 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. > > > -- Sent from my BlackBerry. Ignore the typos unless they're funny.
