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.
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.
Comment