One Query - Takes Ages - Pointers?

[RMD27]

Number: 33
Time: 1.8555 Seconds.
Query: select m.manufacturers_name, p.products_image, pd.products_name, p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id left join specials s on p.products_id = s.products_id, products_to_categories p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '8716' order by pd.products_name limit 0, 30

 

This in in the index.php file on the page that list the products before you go to the product_info page

 

Any tips to speed this up? Or why it is taking so long?

 

The time the query takes appears to be independent of the number of products on that page

[RMD27]

Okay, a more generic question, could someone tell me the purpose of this query?

 

Does it build the product listing information? ie the page listing the products before you get to the product_info page?

[RMD27]

I have looked into it a bit more this morning and I believe the query must be related to the product listing page.

 

When I reduce the number of product to be displayed the number of queries drops massively and the query is slightly quicker.

 

I would prefer for the page to list ideally 100 products.

 

I could live without the prices being displayed on that page so is it possible to cut prices from the query to ligthen the load?

 

I have tried putting a "0" by the price in the admin section so the prices do not show but the query does not appear to change.

 

If anyone has a tip on how to eliminate the price from the query it would be appreciated, or alternatively it is possible to break the query down into more manageable chunks maybe that would speed things up?

 

Any input appreciated

[♥kymation]

Reducing the fields that are returned won't help. I would start by looking at the table indexes. osCommerce 2.3.1 has the fields indexed that need to be for this query. You didn't say which version you are running, but if it's an older version, try looking at the database for 2.3.1 to see if you need to add an index.

 

Regards

Jim

[RMD27]

Hello Jim

 

Its a 2.2RC2 store running around 150,000 products

 

I think I recall running a query to create indexes a while ago but it was a copy and paste job

 

My zero knowledge will expose itself with my next question

 

How do I check if I have/need indexes to sort this issue?

[♥kymation]

Keep in mind that I'm not an expert on MySQL. I usually just try things and observe the result

 

You can check the index(es) on a table using your database admin tool. The Structure tab usually shows Indexes at the bottom. Look at each table in your query and make note of the existing indexes.

 

The following could benefit from having an index:

1. Any field used in a join.

2. Any field used in a where clause.

3. Any field used in a order by clause.

 

I would add an index on one field at a time and check to see if it made an improvement. An index will not always help; the result depends on a number of factors. If you don't see an improvement, try something else.

 

Regards

JIm

[RMD27]

Hello Jim

 

I cannot thank you enough for taking the time to help me out

 

I tried what you said, most of the changes I made actually increased the query time.

 

Making indexes for product_id columns were the biggest culprits for increasing the query time

 

But and after a bit of chopping and changing I created an index for the language_id column in the product description table and the query time has been annihilated

, from an average of 1.8 seconds down to an average query time of 0.04seconds

 

Thank you again!

[♥kymation]

The product_id fields should already have an index. Adding a second index to a field that already has one will not help. The key is to find a field that does not have an index and add one. Sorry if I didn't make that clear.

 

Regards

Jim