Cant show products in large category on frontend or backend

[randomdev]

I have a product category with about 6000 products and today cannot seem to get a list of the products in the frontend or the backend? (smaller categories work ok)

Here is the error from the backend when i try to show the products in the category

Code Select Expand

vmError: exeSortSearchListQuery Incorrect key file for table '/tmp/#sql_738e_0.MYI'; try to repair it SQL=SELECT SQL_CALC_FOUND_ROWS * FROM `xxxx_virtuemart_products_en_gb` as l JOIN `xxxx_virtuemart_products` AS p using (`virtuemart_product_id`) LEFT JOIN `xxxx_virtuemart_product_categories` ON p.`virtuemart_product_id` = `xxxx_virtuemart_product_categories`.`virtuemart_product_id` LEFT JOIN `xxxx_virtuemart_categories_en_gb` as c ON c.`virtuemart_category_id` = `xxxx_virtuemart_product_categories`.`virtuemart_category_id` WHERE ( `xxxx_virtuemart_product_categories`.`virtuemart_category_id` = 164) group by p.`virtuemart_product_id` ORDER BY p.`created_on` DESC LIMIT 0, 10

If I paste this sql into phpmyadmin it also fails, however if I remove the order by statements it will run ok. Is there anything I can do to optimise this statement to make it not fail?

From googling it either sounds like this sql is not good or the tmp folder on the hosting is full (I checked and from what I could tell it looked like there was free space on the hosting)

Has anyone got any suggestions/soltutions? (I have tried optimising and repairing tables without any success)

[Milbo]

You do not write the version, please use vm2.0.9, it has enhanced product listing.