[SOLVED] MySQL Query Optimization needed

[friedemann_bach]

Hello,

 

I am administering a bibliographic database and need some help with a query issue.

 

The database has a simple structure: it contains a table of 6.000 titles (t), a table of 11.000 keywords related to the titles (k) and a n:m table that connects titles to keywords (tk).

 

When searching for a specific keyword, I want not only to display the related titles, but also "related keywords": I need those keywords which would show up a different list of titles. (It happens sometimes that a keyword is given exactly to the same group of titles already displayed, so that it would reproduce the same list of titles again, which I want to avoid.)

 

I designed the following query ($user_keyword_id is the keyword selected by the user):

 

SELECT
  k.*
FROM
  keywords k,
  titles_keywords tk1, /* all titles related to the user keyword */
  titles_keywords tk2, /* all keywords related to the titles in tk1 */
  titles_keywords tk3  /* all keywords related to the titles in tk2, but not related to the titles in tk1 */
WHERE
  tk1.keyword_id=$user_keyword_id
  AND tk1.title_id=tk2.title_id
  AND tk3.keyword_id=tk2.keyword_id
  AND NOT tk3.title_id=tk1.title_id
  AND tk3.keyword_id=k.id
GROUP BY k.id
ORDER BY k.schlagwort

This produces the result I want, though it will take a long time in most cases. I think that the query should be optimized. Can anyone help?

 

[fenway]

First, re-write the query using JOIN syntax:

 

ELECT
  k.*
FROM
  keywords k
  JOIN titles_keywords tk1 ON ( tk1.keyword_id=k.id )
  JOIN titles_keywords tk2 ON ( tk1.title_id=tk2.title_id )
  JOIN titles_keywords tk3 ON ( tk3.keyword_id=tk2.keyword_id )
WHERE
  tk1.keyword_id=$user_keyword_id
  AND tk3.title_id!=tk1.title_id
GROUP BY k.id
ORDER BY k.schlagwort

Post the EXPLAIN output from both queries and we'll take it from there.