> -----Original Message----- > From: Brad Guillory > Sent: Thursday, February 03, 2005 18:15 > > Please be gentle, I have nearly no experience with SQL > databases. I am > not subscribed to the list so please cc me on replies. > > Because my email client probably did horrable things to this > post you > can find the text here also: > http://nolab.org/scratch/mysql-index-oddness.html > > I use syslog-ng to log syslog events to a mysql database. > Retreiving > data was taking 5-10 minutes so I decided to try to learn a > bit about > indexes to try to speed things up. This is what I ended up > setting up. > Database: syslog Table: logs Rows: 186422 > +----------+------------------+------+-----+---------+-------- > -------- > +---------------------------------+ > | Field | Type | Null | Key | Default | Extra > | > Privileges | > +----------+------------------+------+-----+---------+-------- > -------- > +---------------------------------+ > | host | varchar(32) | YES | MUL | | > | > select,insert,update,references | > | facility | varchar(10) | YES | MUL | | > | > select,insert,update,references | > | priority | varchar(10) | YES | MUL | | > | > select,insert,update,references | > | level | varchar(10) | YES | | | > | > select,insert,update,references | > | tag | varchar(10) | YES | | | > | > select,insert,update,references | > | date | date | YES | MUL | | > | > select,insert,update,references | > | time | time | YES | MUL | | > | > select,insert,update,references | > | program | varchar(15) | YES | | | > | > select,insert,update,references | > | msg | text | YES | | | > | > select,insert,update,references | > | seq | int(10) unsigned | | PRI | | > auto_increment | > select,insert,update,references | > +----------+------------------+------+-----+---------+-------- > -------- > +---------------------------------+ > > > mysql> SHOW INDEX FROM logs; > +-------+------------+----------+--------------+------------- > +-----------+-------------+----------+--------+------+------------ > +---------+ > | Table | Non_unique | Key_name | Seq_in_index | Column_name | > Collation | Cardinality | Sub_part | Packed | Null | Index_type | > Comment | > +-------+------------+----------+--------------+------------- > +-----------+-------------+----------+--------+------+------------ > +---------+ > | logs | 0 | PRIMARY | 1 | seq > | A > | 186422 | NULL | NULL | | BTREE | | > | logs | 1 | host | 1 | host > | A > | 2 | NULL | NULL | YES | BTREE | | > | logs | 1 | time | 1 | time > | A > | 2741 | NULL | NULL | YES | BTREE | | > | logs | 1 | date | 1 | date > | A > | 1 | NULL | NULL | YES | BTREE | | > | logs | 1 | priority | 1 | priority > | A > | 2 | NULL | NULL | YES | BTREE | | > | logs | 1 | facility | 1 | facility > | A > | 2 | NULL | NULL | YES | BTREE | | > +-------+------------+----------+--------------+------------- > +-----------+-------------+----------+--------+------+------------ > +---------+ > 6 rows in set (0.00 sec) > > Based on a short tutorial that I found I decided to look at > how these > new indexes would affect my SELECT using the EXPLAIN command: > > mysql> EXPLAIN SELECT * FROM logs WHERE host in > ('10.20.254.5') and > date='2005-02-03' and HOUR(time) between '16' and '17' > ORDER BY seq > DESC;
ALTER TABLE logs ADD unique (host,date,time,seq); You may have to run myisamchk -a, or ANALYZE TABLE after this. http://dev.mysql.com/doc/mysql/en/myisamchk-syntax.html http://dev.mysql.com/doc/mysql/en/myisamchk-other-options.html This should help the performance of this query. > +-------+------+---------------+------+---------+------+-------- > +-----------------------------+ > | table | type | possible_keys | key | key_len | ref | rows > | Extra > | > +-------+------+---------------+------+---------+------+-------- > +-----------------------------+ > | logs | ALL | host,date | NULL | NULL | NULL | > 139817 | Using > where; Using filesort | > +-------+------+---------------+------+---------+------+-------- > +-----------------------------+ > 1 row in set (0.00 sec) > > mysql> EXPLAIN SELECT * FROM logs WHERE priority="warning" > ORDER BY > seq DESC; > +-------+------+---------------+------+---------+------+-------- > +-----------------------------+ > | table | type | possible_keys | key | key_len | ref | rows > | Extra > | > +-------+------+---------------+------+---------+------+-------- > +-----------------------------+ > | logs | ALL | priority | NULL | NULL | NULL | > 139817 | Using > where; Using filesort | > +-------+------+---------------+------+---------+------+-------- > +-----------------------------+ > 1 row in set (0.00 sec) > > mysql> EXPLAIN SELECT * FROM logs WHERE priority="notice" ORDER BY > seq DESC; > +-------+------+---------------+----------+---------+-------+------ > +-----------------------------+ > | table | type | possible_keys | key | key_len | ref | rows | > Extra | > +-------+------+---------------+----------+---------+-------+------ > +-----------------------------+ > | logs | ref | priority | priority | 11 | const | 4003 | > Using where; Using filesort | > +-------+------+---------------+----------+---------+-------+------ > +-----------------------------+ > 1 row in set (0.00 sec) > > It doesn't look like it is helping at all for any but the > last SELECT. > There are several things that I don't understand. > > Why does the second query not benefit from the index but the > third does? My guess is that most of your rows have priority="warning". Due to the cardinality of the index, the optimizer knows that it will not really benefit from this index. In the case of priority="notice", the query will benefit from the index. You could add the following index, but I don't know how useful these last queries will be since both return a lot of rows. ALTER TABLE logs ADD UNIQUE (priority,seq); > Why does the select say "Using filesort" but seq is indexed? The column used to restrict rows s not part of the key, therefore this index will not help. The above index will fix this. You may want to read the following. It explains how mysql uses indexes with the order by clause. http://dev.mysql.com/doc/mysql/en/order-by-optimization.html > Do I need to make a special index to index time on HOUR? Is it even > possible? I believe the index on time will work for this. > > Thanks, BMG > > --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
