None of these selects will use an index. 1. An index can not be used for LIKE '$something'. 2. An index will not be used if 1/3 of the entries match. 3. See #1.
Jaime Teng wrote: >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 > > --------------------------------------------------------------------- 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
