List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:March 25 2002 3:17pm
Subject:Re: slow search on INDEX?
View as plain text  
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
>
>


Thread
slow search on INDEX?Jaime Teng25 Mar
  • Re: slow search on INDEX?Gerald Clark25 Mar