friedemann_bach Posted October 18, 2007 Share Posted October 18, 2007 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? Link to comment https://forums.phpfreaks.com/topic/73769-solved-mysql-query-optimization-needed/ Share on other sites More sharing options...
fenway Posted October 19, 2007 Share Posted October 19, 2007 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. Link to comment https://forums.phpfreaks.com/topic/73769-solved-mysql-query-optimization-needed/#findComment-373140 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.