Hi, I have this table: 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(10) | | MUL | | | | description | char(100) | | | | | +-------------+------------------+------+-----+---------+----------------+ Currently, this table has 300,000+ entries and 39MB in filesize.
I made some observations based on the following search: 1. SELECT * FROM eventlog WHERE description LIKE "%DENIED%" ORDER BY id DESC LIMIT 20; 2. SELECT * FROM eventlog WHERE source = 'ERROR' ORDER BY id DESC LIMIT 20; 3. SELECT * FROM eventlog WHERE SOURCE = 'ERROR' and description LIKE "%DENIED%" ORDER BY id DESC LIMIT 20; Of the 3 queries, #1 gives me the fastest result (<5 secs), and #2 comes very far second (>10 secs), and #3 comes ALMOST equal in time with #2. I'd like to point out that the pattern '%DENIED%' is only present when source = 'ERROR'. AND source='ERROR' is about 1/3 of the table's entry. Furthermore, the pattern '%DENIED%' occurs 1/5 of the time. Considering the fact that I do not have any INDEX on description, the search *description LIKE "%DENIED"* should be very slow as this will try to apply the search on ALL entries. Likewise, since I have an INDEX on source, the search source='ERROR' should be fairly fast at least faster than the first one. YET, I dont get any improvement and it turned out even a lot slower. Why was it this way? thanks for your inputs. 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
