Announcement

Announcement Module
Collapse
No announcement yet.

Please help me with these queries

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Please help me with these queries

    Hello everyone!

    I have a problem with my query "group by" section making every query create a temporary table and doing filesort. At least that's where I think the problem is.

    I'm making a small application for stock management.
    The products that are on stock are a bunch of boxes in different sizes, widths and colors.
    Each entry (box) has a category (based on it's purpose) "cat_ID", color "color_ID", width "width_ID", size "size_ID", group relation "group_box_ID", parent relation (model) "main_box_ID" and a few other parameters.

    As you will see in the attached PhpMyAdmin export there is a "main" box which doesn't have a color, width or size assigned to it. It's just a placeholder for now - later it will hold a description. I call this the main model ("parent" if you'd like).

    Each main-model box contains several groups (group_box_ID) of boxes. A group related to a main-model box is _always_ _different_ when the color is different. Every group has several variations of that box in the same color (different width, or different size)

    On the product list page I need to create a dropdown list of available widths, sizes and colors based on the purpose selected and I do not want to show every single variation, but just unique colors in each group.

    So here is how I'm doing it now:

    SELECT * FROM box_products WHERE in_stock>0 AND waterproof='yes' AND enabled='1' AND cat_ID='8' GROUP BY group_box_ID ORDER BY box_ID DESC


    I generate a list of available sizes by querying for:

    SELECT distinct(size_ID) FROM box_products WHERE in_stock>0 AND waterproof='yes' AND enabled='1' AND cat_ID='8' GROUP BY group_box_ID


    (and the same goes for the widths and colors).

    I do not want to group them by color_ID because I will get all of the variations - same box in black will be shown several times because it comes in regular and narrow width, each in 2 different sizes, instead of just 1.

    When a width is selected I do the following:

    SELECT * FROM box_products WHERE in_stock>0 AND waterproof='yes' AND enabled='1' AND cat_ID='8' AND width_ID='2' GROUP BY group_box_ID ORDER BY box_ID DESC


    The attached SQL is a phpMyAdmin export of 50 entries.
    It's not a problem on this small scale but the database is going to grow beyond 1000 entries.

  • #2
    The last query will always require a filesort. The other two should be easy to optimize: look into multi-column indices.

    Comment

    Working...
    X