Everytime Prestashop Index.php MySQL query is run it consumes a considerable amount of CPU time

[usengines]

Received this message after are Prestashop was automatically suspended for consuming an inordinate amout of Server processing time. As you can see below they are threatening to suspend the account unless the script is optimized. The script in this case is stock 1.54 Prestashop was downloaded from Hostgators Quick Install servers.

 

Hello,

 

Everytime your home page at index.php is loaded the following MySQL query is run and consumes a considerable amount of processor time:

 

+----------------+----------+-------------------+-----------+----------------+------------+-----------------+--------------------+------------------+

| ID | DB | COMMAND | TIME | STATE | LEFT(INFO,1440)

+----------------+----------+-------------------+-----------+----------------+------------+-----------------+--------------------+------------------+

| 70371 | _______ | Query | 13 | Sending data | SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`,

pl.`meta_keywords`, pl.`meta_title`, pl.`name`, MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` AS manufacturer_name,

DATEDIFF(

product_shop.`date_add`,

DATE_SUB(

NOW(),

INTERVAL 20 DAY

)

) > 0 AS new,

MAX(product_attribute_shop.id_product_attribute) id_product_attribute

FROM `ps_product` p

INNER JOIN ps_product_shop product_shop

ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)

LEFT JOIN `ps_product_lang` `pl` ON

p.`id_product` = pl.`id_product`

AND pl.`id_lang` = 1 AND pl.id_shop = 1

LEFT JOIN `ps_image` `i` ON i.`id_product` = p.`id_product`

LEFT JOIN ps_image_shop image_shop

ON (image_shop.id_image = i.id_image AND image_shop.id_shop = 1 AND image_shop.cover=1)

LEFT JOIN `ps_image_lang` `il` ON i.`id_image` = il.`id_image` AND il.`id_lang` = 1

LEFT JOIN `ps_manufacturer` `m` ON m.`id_manufacturer` = p.`id_manufacturer`

LEFT OUTER JOIN `ps_product_attribute` pa ON p.`id_product` = pa.`id_product`

LEFT JOIN ps_product_attribute_shop product_attribute_shop

ON (product_attribute_shop.id_product_attribute = pa.id_product_attribute AND product_attribute_shop.id_shop = 1 AND product_attribute_shop.default_on = 1)

LEFT

JOIN ps_stock_available stock

ON (stock.id_pro |

+----------------+----------+-------------------+-----------+----------------+------------+-----------------+--------------------+------------------+

 

Your site was being lightly crawled by google and MSN at the time of the suspension but since the queries took so long to finish, many instances were running all at once which triggered the automatic suspension.

 

As this suspension was temporary, the account has already been unrestricted. However, this suspension indicates that your website/script may need optimization. If any changes you implement are not effective in reducing it's impact on the server, we may be forced to re-suspend your account.

 

I recommend continuing to reduce the impact your site has on the server to avoid this situation in the future. Generally, adding indexes to database tables, dropping excess tables or rows, or optimizing the queries themselves may improve performance. If you're not sure what you're doing, it's best not to modify any table and caution is recommended.

 

Let us know if you have questions.

 

Best wishes,

 

Martin B

HostGator.com LLC

http://support.hostgator.com