Why is this query so slow?

[youradds]

Hi,

I'm trying to do some optimizing of the tables, and I found this query which is taking 0.15 seconds to run:

SELECT *
FROM glinks_Links
WHERE (
Options LIKE '%30 minute meals%'
AND Type = 'Recipe'
AND isValidated = 'Yes'
)
ORDER BY Sponsored_c DESC
LIMIT 0 , 25

The table is reasonbly large (maybe 25k rows). The fields are set as:

isValidated = ENUM
Type = ENUM
Option = VARCHAR(100)

I've tried moving the "Options" bit *AFTER* Type/isValidated, but it still seems too slow. Removing the "Options" part of the query drops it down to 0.0045 seconds.. which is more like what I would expect!

I've got an index setup for the query... but maybe there is more I can do?

Options_2	BTREE	No	No	Options	3066	A	Yes	
Type	3066	A	No
isValidated	3066	A	No
Sponsored_c	3066	A	No

TIA

Andy

 

[youradds]

Mmm interesting - If I use USE INDEX it seems quite a bit quicker:

SELECT * FROM glinks_Links USE INDEX (Options_2) WHERE (Options LIKE '%30 minute meals%' AND Type = 'Recipe' AND isValidated = 'Yes') ORDER BY Sponsored_c DESC LIMIT 0, 25;

...now only 0.035 seconds. A "USE INDEX" isn't ideal though, as the Perl module I'm using doesn't allow for you to specify a force index

Cheers

Andy