CODEWITHSUNDEEP
mysql
Fabrizio
Fabrizio

Reputation: 3776

Why doesn't use Indexes?

I have a simple query: 

SELECT id
FROM logo
WHERE (`description` LIKE "%google%" AND `active` = "y")
ORDER BY id ASC

the table logo (MyISAM) is formed by about 30 fields with 2402024 rows in it. the field description is a varchar(255) not null. the field active is an ENUM('y','n') not null the cardinality of those indexes are: 

`active`: BTREE Card. 2 
`description`: BTREE Card. 200168

EXPLAIN on the query returns this: 

select_type: SIMPLE 
table: logo 
type: ALL 
possible_keys: active 
key: NULL 
key_len: NULL 
ref: NULL 
rows: 2402024 
Extras: Using where

I am wondering why the query is not using the description index and how can I optimize the table so this query will run smoothly without a full table scan

Already optimized the table and checked for errors..

Upvotes: 0

Views: 52

Answers (1)

Robert Harvey
Robert Harvey

Reputation: 180787

Given that your LIKE condition has a % at the beginning and end of the expression being compared, an index is useless in this context.

An index is only useful if the comparison expression can be found in an "ordered" fashion, as in A comes before B, which comes before C. In other words, LIKE 'google%' may use an index, while LIKE '%google%' will not.

Upvotes: 2

Related Questions