Eddie Shipman
asked on
Help optimizing this query...
SELECT p.*, pa.`id_product_attribute`, pl.`description`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name, tl.`name` AS tax_name, t.`rate`, cl.`name` AS category_default, DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL 20 DAY)) > 0 AS new,
(p.`price` * ((100 + (t.`rate`))/100) - IF((DATEDIFF(`reduction_from`, CURDATE()) <= 0 AND DATEDIFF(`reduction_to`, CURDATE()) >=0) OR `reduction_from` = `reduction_to`, IF(`reduction_price` > 0, `reduction_price`, (p.`price` * ((100 + (t.`rate`))/100) * `reduction_percent` / 100)),0)) AS orderprice
FROM `ps_category_product` cp
LEFT JOIN `ps_product` p ON p.`id_product` = cp.`id_product`
LEFT JOIN `ps_product_attribute` pa ON (p.`id_product` = pa.`id_product` AND default_on = 1)
LEFT JOIN `ps_category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = 1)
LEFT JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 1)
LEFT JOIN `ps_image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
LEFT JOIN `ps_image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = 1)
LEFT JOIN `ps_tax` t ON t.`id_tax` = p.`id_tax`
LEFT JOIN `ps_tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = 1)
LEFT JOIN `ps_manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
WHERE cp.`id_category` = 14 AND p.`active` = 1
ORDER BY `orderprice` ASC
LIMIT 10,10
Here is the EXPLAIN:"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
"1","SIMPLE","pa","system","product_attribute_product,product_default",NULL,NULL,NULL,"0","const row not found"
"1","SIMPLE","cp","range","category_product_index,id_product","category_product_index","4",NULL,"135000","Using where; Using index; Using temporary; Using filesort"
"1","SIMPLE","p","eq_ref","PRIMARY","PRIMARY","4","partrepo_store.cp.id_product","1","Using where"
"1","SIMPLE","cl","eq_ref","category_lang_index","category_lang_index","8","partrepo_store.p.id_category_default,const","1",""
"1","SIMPLE","pl","eq_ref","product_lang_index,id_lang","product_lang_index","8","partrepo_store.p.id_product,const","1",""
"1","SIMPLE","i","ref","image_product,product_position,id_product_cover","id_product_cover","5","partrepo_store.p.id_product,const","12",""
"1","SIMPLE","il","eq_ref","image_lang_index,id_image","image_lang_index","8","partrepo_store.i.id_image,const","1",""
"1","SIMPLE","t","eq_ref","PRIMARY","PRIMARY","4","partrepo_store.p.id_tax","1",""
"1","SIMPLE","tl","eq_ref","tax_lang_index","tax_lang_index","8","partrepo_store.t.id_tax,const","1",""
"1","SIMPLE","m","eq_ref","PRIMARY","PRIMARY","4","partrepo_store.p.id_manufacturer","1",""
table #rows
---------------------------- -----------
ps_category_product 342,386
ps_product 330,111
ps_product_attribute 0
ps_category_lang 12
ps_product_lang 1,027,155
ps_image 342,384
ps_image_lang 1,027,149
ps_tax 1
ps_tax_lang 3
ps_manufacturer 1
What can be done to optimize this, it currently takes 56 seconds to run even when there is a limit.
ASKER
This query, when run on my host returns this:
Incorrect key file for table '/tmp/#sql_15b5_0.MYI'; try to repair it
Incorrect key file for table '/tmp/#sql_15b5_0.MYI'; try to repair it
ASKER
Well, it's not the calculated column causing the problem. It also runs very slow when I change the ORDER BY to quantity
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Holy bejeezus. What a difference. Thanks a million.
ASKER
Excellent
ASKER
If I run the same query without the ORDER BY it runs in < 1 sec.