mysql - Search a many to many relationship with a wild card, performance issues -

i building database app , testing performance issues on larger data set. generated 250,000 location records. each location can assigned many categories , category can assigned many locations. data-set has 2-4 categories assigned each location.

i want allow user search locations filtering categories should allowed using wild card search. maybe want match categories word "red" in it. if type red, shows locations have category title has "red" in it. in addition, wildcard search location title same string.

i wrote query works performance awful in large data-sets. using inner queries fine if limit set , find results quick (around .05ms). if don't find results right away, looks goes through whole database , query takes around 9-10 seconds.

here simplified layout of database:

locations: id | title | address categories: id | title locations_categories: id | location_id | category_id 

here query using:

select `id`,`title`,`address` (`locations`) title '%string%' , id in (  select location_id   locations_categories   join categories on categories.id = locations_categories.category_id   categories.title '%string%') 

first of all, main query uses value of subquery, can rewritten:

 select location_id   locations_categories   join categories on categories.id = locations_categories.category_id   categories.title '%string%' 

but i'd propose split query in two—joins slow big datasets. first 1 necessary category ids (with paging):

select id categories title '%string%' limit <start>, <step> 

then can locations_categories:

select location_id locations_categories category_id in (...) 

and you'll use location ids you've got retrieve corresponding records:

select * locations id in (...) 

these 3 queries combined faster original one.

also, make sure title column indexed—it can bottleneck. since have wildcard @ start of search term, you'll have use fulltext index here.