[vox-tech] Call for SQL help

[Bryan Richter]

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 <nbs at sonic.net>
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
vox-tech at lists.lugod.org
http://lists.lugod.org/mailman/listinfo/vox-tech