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;
+-------+------+---------------+------+---------+------+-------- +-----------------------------+
| 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?
Why does the select say "Using filesort" but seq is indexed?
Do I need to make a special index to index time on HOUR? Is it even possible?
Thanks, BMG
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
