Creación de indices base de datos

[tuverano.com]

Hola, trabajo con la versión 1.3.1.1 y después de monitorizar mysql durante 2 días observe consultas con unos tiempos de respuesta altos. En esos 2 días recibi 250 visitas cada día.

 

1.7 segundos:

 

SELECT `id_link`, `url`, `new_window` FROM PS_blocklink

 

SELECT * FROM `PS_image_type`WHERE scenes = 1 ORDER BY `name` ASC

 

1.3 segundos:

 

SELECT *

FROM `PS_cms` a LEFT JOIN `PS_cms_lang` b ON (a.`id_cms` = b.`id_cms` AND `id_lang` = 3)

WHERE a.`id_cms` = 3

 

SELECT c.id_cms, cl.link_rewrite, cl.meta_title

FROM PS_cms c

LEFT JOIN PS_cms_lang cl ON (c.id_cms = cl.id_cms AND cl.id_lang = 3)

WHERE c.id_cms IN (3,4,5,6,8,9,12)

 

SELECT *

FROM `PS_carrier` a WHERE a.`id_carrier` = 30

 

SELECT `name`, `meta_title`, `meta_description`, `meta_keywords`, `description_short`

FROM `PS_product` p

LEFT JOIN `PS_product_lang` pl ON (pl.`id_product` = p.`id_product`)

WHERE pl.id_lang = 3 AND pl.id_product = 174 AND p.active = 1

 

 

1.1 segundos:

 

SELECT *

FROM `PS_currency` a WHERE a.`id_currency` = 1

 

 

1.0 segundos:

 

SELECT `price_display_method`

FROM `PS_group`

WHERE `id_group` = 1

 

0.9 segundos:

 

SELECT `id_product_attribute`

FROM `PS_product_attribute`

WHERE `default_on` = 1 AND `quantity` >= 1 AND `id_product` = 267

 

SELECT *

FROM PS_meta m

LEFT JOIN PS_meta_lang ml on (m.id_meta = ml.id_meta)

WHERE m.page = 'index' AND ml.id_lang = 3

 

SELECT c.id_cms, cl.link_rewrite, cl.meta_title

FROM PS_cms c

LEFT JOIN PS_cms_lang cl ON (c.id_cms = cl.id_cms AND cl.id_lang = 3)

WHERE c.id_cms IN (3,4,5,6)

 

SELECT cf.`id_customization_field`, cf.`type`, cf.`required`, cfl.`name`, cfl.`id_lang`

FROM `PS_customization_field` cf

NATURAL JOIN `PS_customization_field_lang` cfl

WHERE cf.`id_product` = 201 AND cfl.`id_lang` = 3

ORDER BY cf.`id_customization_field`

 

Como primera medida he creado indices para los campos (marcados en rojo):

 

ALTER TABLE `PS_image_type` ADD INDEX (scenes)

ALTER TABLE `PS_product` ADD INDEX (active)

ALTER TABLE `PS_product_attribute` ADD INDEX (default_on), ADD INDEX (quantity)

ALTER TABLE `PS_meta` ADD INDEX (page)

 

Alguna sugerencia más ???

 

Trabajo con un proveedor de hosting compartido.

 

Atentamente,

Jpe Franco

[shacker]

prueba nuestro modulo gratuito para optimizar las tablas, haz un backup primero.

http://catalogo-onlinersi.com.ar/en/back-office-prestashop-modules/217-delete-connections.html