SQL optimization

[gvp16]

Hi, all im running this query :

 

SELECT c.content_title, c.content_body,c.content_image,c.permalink, 
co_pn.content_option_value AS Part_No,
co_pn2.content_option_value AS Part_No2, 
co_b.content_option_value AS Brand,
co_b2.content_option_value AS Brand2, 
co_r.content_option_value AS RRP, 
co_wp.content_option_value AS Web_Price,
co_cl.content_option_value AS Clearance,
co_t.content_option_value AS Tax_ID,
co_pr.content_option_value AS Price, 
co_col.content_option_value AS Colour, 
co_sz.content_option_value AS Size 

FROM category_items 
LEFT JOIN content c ON category_items.content_id = c.content_id 
LEFT JOIN content_values co_pn ON c.content_id = co_pn.content_id AND co_pn.content_option_id = 'Part_No' 
LEFT JOIN content_values co_pn2 ON c.content_id = co_pn2.content_id AND co_pn2.content_option_id = 'Part_No2' 
LEFT JOIN content_values co_b ON c.content_id = co_b.content_id AND co_b.content_option_id = 'Brand'
LEFT JOIN content_values co_b2 ON c.content_id = co_b2.content_id AND co_b2.content_option_id = 'Brand2'
LEFT JOIN content_values co_r ON c.content_id = co_r.content_id AND co_r.content_option_id = 'RRP' 
LEFT JOIN content_values co_wp ON c.content_id = co_wp.content_id AND co_wp.content_option_id = 'Web_Price'
LEFT JOIN content_values co_cl ON c.content_id = co_cl.content_id AND co_cl.content_option_id = 'Clearance' 
LEFT JOIN content_values co_t ON c.content_id = co_t.content_id AND co_t.content_option_id = 'Tax_ID'
LEFT JOIN content_values co_pr ON c.content_id = co_pr.content_id AND co_pr.content_option_id = 'Price' 
LEFT JOIN content_values co_col ON c.content_id = co_col.content_id AND co_col.content_option_id = 'Colour' 
LEFT JOIN content_values co_sz ON c.content_id = co_sz.content_id AND co_sz.content_option_id = 'Size' 

WHERE category_items.category_id = '10937' GROUP BY Part_No2 LIMIT 0,50

 

which basically selects basic product information from the content table and all its different options from a table a content values table, this works fine when a few results are loaded, however some times its taking up to 90 seconds to return the results. How can i improve the performance of the query? or have I just gone about it wrong?

 

Ive indexed the tables, but im not sure if I have done it correctly 

 

Thanks.

[requinix]

Do you have indexes on the appropriate columns?

[gvp16]

I have indexes on the auto increment columns and referencing columns, eg content_id.

 

Is that correct?

[requinix]

On content_option_id too?

 

What does

EXPLAIN SELECT (the rest of the query...)

show?

[DavidAM]

Why is there a GROUP BY in there? You are not using any grouping functions in the query.

[gvp16]

yes on content_option_id as well, I have emptied the database a few times thought through testing, do i need to rebuild the index, if so how do i do that?

 

this is what explain returns :

 

Im using group at the end because the query results a list of products and its sizes/ colours, so I group on part_no2 (which is the part no minus the size or colour) and as such I get just one product instead of 4/5. on the product page there is then an option to select colour/ size.

 

Thanks for the help guys.

[mikosiko]

just wondering why you need so many JOINS against the table content_values... seems to me that probably only one JOIN using IN() and a couple CASE's in the SELECT portion will work... but I'm just guessing.... without knowing your tables descriptions, some data example and expected results (hence your objectives) is hard to tell.  GROUP BY is not necessary in the current query as DavidM said.

[gvp16]

im using the content_values table to store extra fields for products in the content table.

 

my thinking was that i didnt want to have all the information in one table, im left joining them so i can search by the values for each products.

 

If you think I can do it a better way then I would be great full for you help.

 

Thanks.

[mikosiko]

....

If you think I can do it a better way then I would be great full for you help.

 

Thanks.

 

As I said... just guessing... without knowing your tables descriptions, some data example and expected results (hence your objectives) is hard to tell

[gvp16]

ok, I have content table which holds the basic information about a product such as:

 

content_id,  content_title        ,description,          image,            meta_tags,            visible

1                  test product          description....        1234.jpg          metatags...            1

 

and content_values to hold any additional fields that are needed

 

content_option_id,    content_option_value,      content_id

Part_No                            1234                                  1

Brand                                Ford                                    1

Price                                  12.00                                  1

Colour                              Blue                                      1

 

 

each product would have a similar set up

 

when the product is pulled from the database I would like all the attributes returned with a single query, so I dont have to add additional queries/ searches to get all the information

 

Thanks.

[mikosiko]

if your objective is generate a query that return only one row for each content_id, then your query is doing that, however as soon as you decide to add a new content_value (in case a new business requirement) you will need to modify the query to add the new condition (which to me, seems a bad Db design to start with, but that is a different topic).

 

with the situation/configuration on hand  I will probably simplify the query to return the full JOIN'ed record set, and I will post-process the result (using an array maybe) according to what is needed at display time... but that is just me.

 

 

[gvp16]

How would I go about simplifying the query? I havent done alot using joins like this so its all new to me at the moment.

[mikosiko]

again... depend on your objectives... if your objective is generate a query that return only one row for each content_id, then your query is doing that

 

as an example only (could be not valid for your objectives) try this and think how you can post-process the results and if that fit your goals

SELECT c.content_title, 
             c.content_body,
             c.content_image,
             c.permalink, 
             cv.content_option_id,
             cv.content_option_value
FROM category_items 
          LEFT JOIN content c ON category_items.content_id = c.content_id 
          LEFT JOIN content_values cv ON c.content_id = cv.content_id
WHERE category_items.category_id = '10937'

 

if that doesn't serve you I will let others with more expertize to help you to optimize your current query

[mikosiko]

by the way... will help a lot if you post your full table descriptions including the indexes for each one

[gvp16]

ok I will do that shortly, what ive got works fine for what I need, just the problem is its so damn slow when loading up more than a few rows

[gvp16]

As requested

 

 

Thanks

[mikosiko]

test if you get improvement with this changes,  on your second table

 

- Drop the index value_index_id  ... it is a duplicate from the KEY index

- Add an index on the column content_id

[gvp16]

WOW, a massive improvement, it now runs about 0.220s rather than 90s!

 

Thanks so much, i didnt spot that i had a duplicate index.

[mikosiko]

the real gain is mainly because the index that you just added on content_id

[gvp16]

lesson learned thanks so much for you help.