1. Definetely break out subqueries. A possible starting point would be creating a temp table of interesting categories. Temp tables are indispensible.
2. Run "explain select ...". Find the silly decisions mysql is using, or the places where an index would help. This ties into bullet (1): you are generally smarter than mysql at breaking up complex queries. 3. If i wan't taking a break from work, where i'm writing sql queries, i'd have a little more time to give a fuller answer. :) -original message- Subject: [vox-tech] Call for SQL help From: Bill Kendrick <[email protected]> Date: 2009-04-16 17:42 You know something's not good when MySQL's logs reports the following for a query: # Query_time: 87 Lock_time: 0 Rows_sent: 209 Rows_examined: 2619608 We've got a search that looks something like this (simplified/obfuscated to hide our secret sauce... or something :) ) SELECT DISTINCT(b.id), b.name, b.datepacked, p.firstname, p.lastname FROM box_category AS bc JOIN boxes AS b ON b.id = bc.boxid LEFT OUTER JOIN people AS p ON b.person = p.username WHERE bc.categoryid IN ( SELECT node.id FROM categories AS node, categories AS parent, categories AS sub_parent, ( SELECT node.id FROM categories AS node, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.id = '4' GROUP BY node.id ORDER BY node.lft ) AS sub_tree WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt AND sub_parent.id = sub_tree.id GROUP BY node.id ) ORDER BY b.datepacked DESC; What we're doing here is saying: * We're looking at category '4' * List all items in category #4 AND items in all subcategories of '4' * Cateogires are stored as a "nested set" model ( http://dev.mysql.com/tech-resources/articles/hierarchical-data.html ) * Boxes are mapped to categories using a separate table ("box_category"), because they can be placed in multiple categories. Are there ways to improve this particular query? The 'boxes' table has over 1000 entries, and growing. The 'categories' table has nearly 1000 entries (the structure only ever goes 3 deep, though, e.g.: Cat->SubCat->SubSubCat). I'm wondering if I should just yank some of the subselects out and construct the final answer by doing: 1. What subcategories are under cat '4'? 2. What boxes are in cat '4' or any of the subcats found in step 1? instead of: 1. What boxes are in cat '4' or any subcategories under cat '4'? Ergh. Make sense? -- -bill! Sent from my computer _______________________________________________ vox-tech mailing list [email protected] http://lists.lugod.org/mailman/listinfo/vox-tech _______________________________________________ vox-tech mailing list [email protected] http://lists.lugod.org/mailman/listinfo/vox-tech
