StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POIncreasing the speed of a query with a sub select?
text
Body
copied!<p>I'm have a question. The following query is taking upwards of 2 - 3 seconds to exicute and I'm not sure why. I have 2 tables involved one with a list of items and the another with a list of attribute's for each item. The items table is indexed with unique primary key and the attributes table has a foreign key constraint.</p> <p>The relationship between the items table is ONE TO MANY to the attributes.</p> <p>I am not sure how else to speed up query and would appreciate any advice.</p> <p>The database is MYSQL inodb</p> <pre><code>EXPLAIN SELECT * FROM eshop_items AS ite WHERE (SELECT attValue FROM eshop_items_attributes WHERE attItemId=ite.ItemId ANd attType=5 AND attValue='20')='20' ORDER BY itemAdded DESC LIMIT 0, 18; id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra 1 PRIMARY ite ALL NULL NULL NULL NULL 57179 Using where; Using filesort 2 DEPENDENT SUBQUERY eshop_items_attributes ref attItemId attItemId 9 gabriel_new.ite.itemId 5 Using where </code></pre> <p>Index: eshop_items_attributes </p> <pre><code>Name Fieldnames Index Type Index method attItemId attItemId Normal BTREE attType attType Normal BTREE attValue attValue Normal BTREE </code></pre> <p>Index: eshop_items</p> <pre><code>Name Fieldnames Index Type Index method itemCode itemCode Unique BTREE itemCodeOrig itemCodeOrig Unique BTREE itemConfig itemConfig Normal BTREE itemStatus itemStatus Normal BTREE </code></pre> <p>Can't use a join because the item_attributes table is a key -> value pair table. So for every record in the items_attributes table there can be many item id's</p> <p>here is a sample</p> <pre><code>item_id attribute_index attribute_value 12345 10 true 12345 2 somevalue 12345 6 some other value 32456 10 true 32456 11 another value 32456 2 somevalue </code></pre> <p>So a join wouldn't work because I can't join multiple rows from the items_attributes table to one row in the items table. </p> <p>I can't write a query where attribute_index is = to 2 AN attribute_index = 10. I would always get back no results. </p> <p>:(</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload