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.
