MySQL Forums
Forum List  »  Optimizer & Parser

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

Hi!

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.*, r.name AS reseller, b.name AS brand, rcat.raw_name AS cats_raw, cats1.name AS cat1, cats1.slug AS catslug1, cats2.name 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:
http://img268.imageshack.us/img268/378/skrmavbild20110309kl015.png


More info about my table structures can be found here:
http://snipt.net/Linuus/mysql-4/?key=1e6e688540f5d5f7d6e29988914368ce


Any ideas how to speed it up a bit?


Best Regards
Linus

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Optimize a query
2866
March 10, 2011 08:59AM
1399
March 13, 2011 03:48PM
1473
March 14, 2011 03:35PM
1484
March 14, 2011 06:26PM
1337
March 15, 2011 02:20PM
1377
March 15, 2011 09:12PM
1279
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.