Hi,
I have a table with several complex indexes:
(source,description,timestamp,id)
(timestamp,id)
as well as individual indexes
(id)
(timestamp)
(type)
(source)
(description)
mysql> describe eventlog;
+-------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | | PRI | NULL | auto_increment |
| timestamp | int(10) unsigned | | MUL | 0 | |
| type | tinyint(4) | | MUL | 0 | |
| source | char(15) | | MUL | | |
| description | char(100) | YES | MUL | NULL | |
+-------------+------------------+------+-----+---------+----------------+
I perform the following search:
SELECT * from eventlog where description like '%...%'
order by timestamp desc, id desc;
SELECT * from eventlog where source = .. description like '%...%'
order by timestamp desc, id desc;
For whatever reason, the second search is consistently very slow (>20 sec)
inspite the fact that there is an index definition to its effect. I find the
first search fast - relatively giving me 0~1 on average, 2~5 at worst.
Was the 1st search fast because it uses only one index (description) while
the 2nd search slow because it uses the complex index (source,description) ?
Jaime
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
