l i n u x - u s e r s - g r o u p - o f - d a v i s
L U G O D
 
Next Meeting:
November 4: Social gathering
Next Installfest:
TBD
Latest News:
Oct. 10: LUGOD Installfests coming again soon
Page last updated:
2009 Apr 17 11:20

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)
Re: [vox-tech] Call for SQL help
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [vox-tech] Call for SQL help



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@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@lists.lugod.org
http://lists.lugod.org/mailman/listinfo/vox-tech
_______________________________________________
vox-tech mailing list
vox-tech@lists.lugod.org
http://lists.lugod.org/mailman/listinfo/vox-tech



LinkedIn
LUGOD Group on LinkedIn
Sign up for LUGOD event announcements
Your email address:
facebook
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:
Appahost Applications
For a significant contribution towards our projector, and a generous donation to allow us to continue meeting at the Davis Library.