MULITPLE JOIN QUERY RUNS SO SLOW -

[jwwceo]

Hey All,

 

I have a site where I am running a few simple queries, such as find by color, find by keyword, etc.

 

I am trying to implement a "search by anything" where a user can enter a term into a text box and get results from any number of fields.

 

It works, but it takes forever to run...like maybe 1-3 minutes. And then after that query is run, the site runs slow unless I close the browser window and open it again. Then all is fine. Its like the query eats all the memory....

 

Has anyone ever heard of this???

 

Here is the query.

 

$dataP = mysql_query("SELECT *
FROM shirts
INNER JOIN shirtcolors on shirtcolors.shirt_id = shirts.shirt_id
INNER JOIN colors ON colors.color_id = shirtcolors.color_id
INNER JOIN shirtkeywords ON shirtkeywords.shirt_id = shirts.shirt_id
INNER JOIN keywords ON shirtkeywords.keyword_id = keywords.keyword_id
WHERE(
colors.color = '$search'
OR keywords.keyword like '%$search%'
OR shirts.name like '%$search%'
OR shirts.keywordlist like '%$search%')
AND active='true' AND approved='1'
GROUP BY shirts.shirt_id ORDER BY shirts.$field $direction
LIMIT $from, $max_results");

 

James

[mwasif]

• How many records do you have?
  
• Did you properly index the columns (columns are being used in JOIN)?
  
• Did you try FULLTEXT instead of LIKE?
  

[fenway]

Where's the EXPLAIN... and, say, a proper query (without php variables)?