Re: Indexing Questions (Problem?)

[Michael Stassen]

Tom Crimmins wrote:
-----Original Message-----
From: Brad Guillory
Sent: Thursday, February 03, 2005 18:15
<snip>
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 index will function as an index on host, as well, so the current 
single-column index on host will be redundant and should be dropped.

  ALTER TABLE logs DROP INDEX host;
Unnecessary indexes slow you down.  In that vein, you should drop any other 
indexes that are unlikely to be used.  For example, unless you will be 
running queries in which you select rows by time *without* regard to date, 
the single-column index on time seems unlikely to be used and should also be 
dropped.

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.
Yes, SHOW INDEX says priority has a cardinality of 2 (higher is better). 
From the EXPLAIN output, 4003 rows have priority = "notice" and 139817 have 
priority = "warning".  Hence the index on priority is useful for "notice" 
and useless for "warning".  Fast notice retrieval but slow warning retrieval 
doesn't sound very helpful.  You should probaly drop the index on priority.

ALTER TABLE logs ADD UNIQUE (priority,seq);
This might help with the ordering.  You should definitely drop the index on 
priority if you add this.

Why does the select say "Using filesort" but seq is indexed?
The column used to restrict rows is 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.
No, it won't.  At least, not with the query as is:
  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;
Once you feed a column through a function, you prevent use of its index. 
Here, using HOUR() on time prevents use of the index on time (or the 3rd 
part of your multi-column index).  Always write your queries to compare 
columns (not functions of columns) to constants, if at all possible.  This 
query should be

  SELECT * FROM logs
  WHERE host = '10.20.254.5'
    AND date='2005-02-03'
    AND time BETWEEN '16:00:00' AND '17:00:00'
  ORDER BY seq DESC;
to allow use of the index on time.
Thanks, BMG
---
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa 
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]