Location switcher slow response time – need assistance

A few more things on SQL queries – There are a few slow queries on each action regarding search and locations. For example, the query for switching location

SELECT SQL_CALC_FOUND_ROWS PREFIXposts.*, PREFIXgeodir_gd_place_detail.*
FROM PREFIXposts 
INNER JOIN PREFIXgeodir_gd_place_detail
ON (PREFIXgeodir_gd_place_detail.post_id = PREFIXposts.ID) 
WHERE 1=1 
AND ( PREFIXposts.post_status = 'publish' 
OR PREFIXposts.post_status = 'private' )
AND PREFIXposts.post_type = 'gd_place'
AND post_locations LIKE '%,[california],%' 
GROUP BY PREFIXposts.ID 
ORDER BY PREFIXposts.post_date DESC, PREFIXposts.post_title ASC 
LIMIT 0, 5

which takes 0.6747sec to be executed and it’s considered slow. Could it be due to a usage LIKE query? Just for fun, I changed the query and used FULLTEXT search on this query and the query took 0.0002sec with the same results. That’s XXX times faster.

LIKE with wildcarding on the left side (IE: LIKE ‘%Search’) can not use an index (assuming one exists for the column), guaranteeing a table scan. LIKE ‘%Search’ and LIKE ‘%Search%’ can not use an index; LIKE ‘Search%’ can use an index as far as I know.

For location switcher, regions and cities are strictly defined values, right? Maybe I’m wrong about this, but why use LIKE if we know where and what to look for? Hope that makes some sort of sense..

For the advanced search.. The database get’s clogged just by typing in the search term. Disabling autocompleter definitely have an impact on performance in good a good way, but as I said above, ‘%Search%’ will always guarantee a full table scan, while ‘Search%’ can use an index.

But still, the fact that the query takes forever with autocompleter enabled, indicates there’s something wrong with it..

Thoughts?