Date: 07/30/07 (MySQL Communtiy) Keywords: mysql, sql I have a table with 341K records. When I query for all records with a certain string that have occurred in the in the last 24 hours, it returns in .66 seconds. When I sort that same query by any field, it returns in 3 minutes. Any thoughts on how to optimize this query for sorting? mysql> describe eventlog_data ;
+----------+---------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------------------+-------+
| host | int(11) | NO | MUL | 0 | |
| event | int(11) | NO | | 0 | |
| record | bigint(20) | NO | | 0 | |
| cname | varchar(255) | NO | | | |
| time | datetime | NO | | 0000-00-00 00:00:00 | |
| type | varchar(16) | NO | | | |
| log | varchar(16) | NO | | | |
| source | varchar(128) | NO | | | |
| message | text | NO | | | |
| notified | enum('y','n') | NO | | n | |
+----------+---------------+------+-----+---------------------+-------+
10 rows in set (0.02 sec)
mysql> select count(record) from eventlog_Data ;
+---------------+
| count(record) |
+---------------+
| 341414 |
+---------------+
1 row in set (0.00 sec)
mysql> SELECT cname, time, event, log
-> FROM eventlog_data
-> WHERE time > SUBDATE(NOW(), INTERVAL 1 DAY)
-> AND eventlog_data.message LIKE '%adm%';
SOME DATA RETURNED
16 rows in set (0.66 sec)
mysql> SELECT cname, time, event, log
-> FROM eventlog_data
-> WHERE time > SUBDATE(NOW(), INTERVAL 1 DAY)
-> AND eventlog_data.message LIKE '%adm%';
-> ORDER BY 1
SOME DATA RETURNED
16 rows in set (2 min 53.63 sec)
|