[SOLVED] Incorrect key file for table...

[MrBaseball34]

When selecting the sort by "price: lowest first" option in my categories page, I'm getting this

Incorrect key file for table '/tmp/#sql_15b5_0.MYI'; try to repair it

       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 0,10

Obviously it is a temp table but how do I remedy this?

[MrBaseball34]

This happens when you select ANY option except the default '--'

[MrBaseball34]

Problem was in the fact that the tmp partition was too small.
Works now that host modified it.

However, I still think the query needs to be optimized.

[MrBaseball34]

Oh, but there is. I am writing up an explanation on how to perform this and will post it later.

[MrBaseball34]

Ok, after further examination and tests, the process by which I was optimizing the query will not work as desired.

Here was the code to optimize the query above:

SELECT * FROM (
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
LIMIT 0,10
) ORDER BY `orderprice` ASC

However, when modifying the query in Category::getProducts(), the meaning of the orderby would be defeated because this only orders the 10 that is returned, not the entire catalog before limiting to 10. (does that make sense?)

The calculation, however, is a problem that I have reported as a bug to the team.
If you don't have a tax, the t.`rate` returns NULL so none of your calculations work as planned.
The t.`rate` in the calculation should be replaced with IFNULL(t.`rate`, 0.0)