# Please help me with these queries
**URL:** https://forums.percona.com/t/please-help-me-with-these-queries/1351
**Category:** Other MySQL® Questions
**Created:** [February 17, 2010, 8:45am UTC](https://forums.percona.com/t/please-help-me-with-these-queries/1351 "2010-02-17T08:45:34Z")
**Posts on this page:** 2
**Page:** 1
### Author:  [@DaveE](https://forums.percona.com/u/DaveE)
#### Post date: [February 17, 2010, 8:45am UTC](https://forums.percona.com/t/please-help-me-with-these-queries/1351/1 "2010-02-17T08:45:34Z")
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.
---
### Author:  [@gmouse](https://forums.percona.com/u/gmouse)
#### Post date: [February 17, 2010, 12:31pm UTC](https://forums.percona.com/t/please-help-me-with-these-queries/1351/2 "2010-02-17T12:31:38Z")
The last query will always require a filesort. The other two should be easy to optimize: look into multi-column indices.