Why is GROUP BY so slow with this...

jvanv8

I have a table that contains attribute values for products. The primary key is product_id, attr_id and the only other field is attr_val (text) which contains the value of the attribute.

I'm trying to create a dropBox of options and populate it with various attr_val 's given an attr_id. The table looks something like:

product_id          attr_id        attr_val (TEXT)
       1               5            Silver
       1               6            325
       2               5            Tangerine Orange
       3               5            Marroon
       4               5            Silver
       5               5            Silver
       6               5            Silver
       7               5            Marroon
       7               6            200

I did not create an attribute_value table that would associate an integer with a attr_val_id such as "Silver=1" because I would also like to be able to execute queries like SELECT * Where attr_id =6 AND attr_val > 300; But maybe I should... ??😕

In this case, I'd like to create a box with the various attr_val for attr_id=5. This should produce selections for Silver, Marroon, and Tangerine Orange.

My query looks like:

$query = "SELECT attr_val FROM product_attr WHERE attr_id = '5' GROUP BY attr_val LIMIT 40";

$result = mysql_query($query);
$num_rows = mysql_num_rows($result);
print('<select name="attr5">');
for($j=0;$j<$num_rows;$j++){
	$row = mysql_fetch_array($result);
	print('<option value="' . $row['attr_val'] . '">' . $row['attr_val'] . '</option>');
}
print('</select>');

(The row checks are just because I was running into problems with too many rows being inserted into the select boxes).

Basically the GROUP BY statement is causing the query to run very slowly (5 sec +). I tried adding a solo attr_id INDEX but it made no difference. If I want to add a attr_val index I will have to change it to a VARCHAR which will truncate many values that are over 255 char.

Thanks for any help!

drawmack

you can't have duplicate entries in the primary key field, check again and make sure you've indexed the table.

jvanv8

The table is indexed properly. Take note that the PRIMARY KEY is product_id, attr_id together.

.. still stumped.

nemonoman

I can't see why this is running slow thought it might be it's because attr_val is TEXT, but text group bys are running very fast for me.

I doubt it will make any difference, but you might try
SELECT DISTINCT attr_val FROM product_attr WHERE attr_id = '5'
and see if there's any diff?

Also doubtful, but can you do your search on the compound primary key by adding a product id to your query? SELECT DISTINCT attr_val FROM product_attr WHERE product_id=1 and attr_id = '5'

Anyway, best would be to get normalized and put Attribute Values into a separate table:

Attribute_Values
attr_id
attr_val

instead of complicating with duplicate values in the product_attr table.

jvanv8

thanks nemonoman!

Nope, I tried the DISTINCT operation and it was the exact same.
I'm definately going to normalize it and store values in a separate table. I might also play around with some subqueries to see if that helps too. Most of the attr_val values are unique which is why I didn't bother normalizing them in the first place (just to save disk space)

The GROUP BY could be taking a while because the table currently has over 3.5 million rows and counting.
🙂

John

nemonoman

3.5 million rows COULD lead to a slow down on a text-based group by. 3.5 million text times to check...have I already seen this value? No. Make a temporary row...Yes. On to the next. Have to compare this value to all the previously stored group by values. Yes this could take a while.

You might have mentioned the number of rows earlier. 5 seconds sounds positively speedy.

As for space considerations: each duplicate value in your attr_text column takes up about as much space as 20 or so rows would take up in a separate table. Where did you get the idea duplicate values would be more space efficient?

jvanv8

hmm... I guess I thought that probably very few rows would be duplicates. The value can be ANYTHING ( 123, black, xxlarge, xxl, 12 Mb, 1.3 GHz, Motorola, USB, Frank Sinatra, 12x Digital Zoom, Cotton, Zimbabwe ... ) you get the idea.. anything can be held here. For CD's, all the song tracks are stored here also. BUT some things WILL be diplicated frequently like if a movie is DVD, VHS, LaserDisc... and I want to select these special cases and place them in select boxes.
... or at least thats the idea. Maybe I'll just go with [gulp] static dropBoxes!! NO!!!:p