l i n u x - u s e r s - g r o u p - o f - d a v i s
Next Meeting:
July 7: Social gathering
Next Installfest:
Latest News:
Jun. 14: June LUGOD meeting cancelled
Page last updated:
2009 Apr 16 18:06

The following is an archive of a post made to our 'vox-tech mailing list' by one of its subscribers.

Report this post as spam:

(Enter your email address)
[vox-tech] Call for SQL help
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

[vox-tech] Call for SQL help

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?

Sent from my computer
vox-tech mailing list

LUGOD Group on LinkedIn
Sign up for LUGOD event announcements
Your email address:
LUGOD Group on Facebook
'Like' LUGOD on Facebook:

Hosting provided by:
Sunset Systems
Sunset Systems offers preconfigured Linux systems, remote system administration and custom software development.

LUGOD: Linux Users' Group of Davis
PO Box 2082, Davis, CA 95617
Contact Us

LUGOD is a 501(c)7 non-profit organization
based in Davis, California
and serving the Sacramento area.
"Linux" is a trademark of Linus Torvalds.

Sponsored in part by:
Sunset Systems
Who graciously hosts our website & mailing lists!