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
