MySQL Forums
Forum List  »  Optimizer & Parser

Optimize a query
Posted by: Linus Pettersson
Date: March 10, 2011 08:59AM


I have read lots of threads about optimizing queries, but I can't make this one any good. So I hope someone could help me here.

I have a database that stores products. Each product has a "raw category" which is mapped to a static category, so one category can be related to many raw categories, one product to one raw category. (Three tables, products<->raw_categories<->static_categories)

I created a VIEW like this:

CREATE OR REPLACE VIEW `jos_clothes_view_products` AS
SELECT tbl.*, AS reseller, AS brand, rcat.raw_name AS cats_raw, AS cat1, cats1.slug AS catslug1, AS cat2, cats2.slug AS catslug2
FROM `jos_clothes_products` AS tbl
LEFT JOIN `jos_clothes_brands` AS b ON b.clothes_brand_id = tbl.clothes_brand_id
LEFT JOIN `jos_clothes_resellers` AS r ON r.clothes_reseller_id = tbl.clothes_reseller_id
LEFT JOIN `jos_clothes_catraws` AS rcat ON rcat.clothes_catraw_id = tbl.clothes_catraw_id
LEFT JOIN `jos_clothes_categories` AS cats2 ON cats2.clothes_category_id = rcat.clothes_category_id
LEFT JOIN `jos_clothes_categories` AS cats1 ON cats1.clothes_category_id = cats2.parent_id

To gather all the data about the products. The categories are in two levels but are stored in the same table. The base categories has parent_id = 0.

The thing is that when I run a simple query like this:
SELECT `tbl`.* FROM `jos_clothes_view_products` AS `tbl` WHERE `tbl`.`cat1` != 'NULL' AND `tbl`.`enabled` = '1' ORDER BY `created_on` DESC , `ordering` ASC LIMIT 0 , 20;

It takes about 30-50 seconds to run sometimes (when not cached I guess...) and it creates a big tmp table, about 300-500MB in size.
The EXPLAIN of my query looks like this:

More info about my table structures can be found here:

Any ideas how to speed it up a bit?

Best Regards

PS. The products table has about 19.000 rows, catraws has 1300 rows and categories about 60 rows.

Options: ReplyQuote

Written By
Optimize a query
March 10, 2011 08:59AM
March 13, 2011 03:48PM
March 14, 2011 03:35PM
March 14, 2011 06:26PM
March 15, 2011 02:20PM
March 15, 2011 09:12PM
March 28, 2011 06:20AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.