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 <mysql-thread104026@stripped>
>To unsubscribe, e-mail
> <mysql-unsubscribe-gerald_clark=suppliersystems.com@stripped>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
