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.