Slow sql queries and long time to first byte

[SkyHiRider]

My Prestashop 1.4.11 time to first byte is 10+ seconds.

 

Using MariaDB 5.5 with caching enabled.

 

I have a hunch that this is because I disabled and enabled caching in the back-office or because I enabled other languages and then disabled them.

Here's my slow query log:

# Time: 150303 20:03:52
# User@Host: database6[database6] @ [88.88.88.88]
# Thread_id: 5307217 Schema: database6 QC_hit: No
# Query_time: 8.402625 Lock_time: 0.001213 Rows_sent: 190 Rows_examined: 380
use database6;
SET timestamp=1425409432;
SELECT *,
`TABLE_SCHEMA` AS `Db`,
`TABLE_NAME` AS `Name`,
`TABLE_TYPE` AS `TABLE_TYPE`,
`ENGINE` AS `Engine`,
`ENGINE` AS `Type`,
`VERSION` AS `Version`,
`ROW_FORMAT` AS `Row_format`,
`TABLE_ROWS` AS `Rows`,
`AVG_ROW_LENGTH` AS `Avg_row_length`,
`DATA_LENGTH` AS `Data_length`,
`MAX_DATA_LENGTH` AS `Max_data_length`,
`INDEX_LENGTH` AS `Index_length`,
`DATA_FREE` AS `Data_free`,
`AUTO_INCREMENT` AS `Auto_increment`,
`CREATE_TIME` AS `Create_time`,
`UPDATE_TIME` AS `Update_time`,
`CHECK_TIME` AS `Check_time`,
`TABLE_COLLATION` AS `Collation`,
`CHECKSUM` AS `Checksum`,
`CREATE_OPTIONS` AS `Create_options`,
`TABLE_COMMENT` AS `Comment`
FROM `information_schema`.`TABLES` t
WHERE BINARY `TABLE_SCHEMA`
IN ('database6')
ORDER BY Name ASC LIMIT 250 OFFSET 0;

# Time: 150303 19:59:37
# User@Host: database6[database6] @ [88.88.88.88]
# Thread_id: 5305443 Schema: database6 QC_hit: No
# Query_time: 7.111693 Lock_time: 0.018424 Rows_sent: 190 Rows_examined: 380
use database6;
SET timestamp=1425409177;
SELECT *,
`TABLE_SCHEMA` AS `Db`,
`TABLE_NAME` AS `Name`,
`TABLE_TYPE` AS `TABLE_TYPE`,
`ENGINE` AS `Engine`,
`ENGINE` AS `Type`,
`VERSION` AS `Version`,
`ROW_FORMAT` AS `Row_format`,
`TABLE_ROWS` AS `Rows`,
`AVG_ROW_LENGTH` AS `Avg_row_length`,
`DATA_LENGTH` AS `Data_length`,
`MAX_DATA_LENGTH` AS `Max_data_length`,
`INDEX_LENGTH` AS `Index_length`,
`DATA_FREE` AS `Data_free`,
`AUTO_INCREMENT` AS `Auto_increment`,
`CREATE_TIME` AS `Create_time`,
`UPDATE_TIME` AS `Update_time`,
`CHECK_TIME` AS `Check_time`,
`TABLE_COLLATION` AS `Collation`,
`CHECKSUM` AS `Checksum`,
`CREATE_OPTIONS` AS `Create_options`,
`TABLE_COMMENT` AS `Comment`
FROM `information_schema`.`TABLES` t
WHERE BINARY `TABLE_SCHEMA`
IN ('database6')
ORDER BY Name ASC LIMIT 250 OFFSET 0;

# Time: 150303 19:54:30
# User@Host: database6[database6] @ [88.88.88.88]
# Thread_id: 5302906 Schema: database6 QC_hit: No
# Query_time: 6.767281 Lock_time: 0.000217 Rows_sent: 190 Rows_examined: 380
use database6;
SET timestamp=1425408870;
SELECT *,
`TABLE_SCHEMA` AS `Db`,
`TABLE_NAME` AS `Name`,
`TABLE_TYPE` AS `TABLE_TYPE`,
`ENGINE` AS `Engine`,
`ENGINE` AS `Type`,
`VERSION` AS `Version`,
`ROW_FORMAT` AS `Row_format`,
`TABLE_ROWS` AS `Rows`,
`AVG_ROW_LENGTH` AS `Avg_row_length`,
`DATA_LENGTH` AS `Data_length`,
`MAX_DATA_LENGTH` AS `Max_data_length`,
`INDEX_LENGTH` AS `Index_length`,
`DATA_FREE` AS `Data_free`,
`AUTO_INCREMENT` AS `Auto_increment`,
`CREATE_TIME` AS `Create_time`,
`UPDATE_TIME` AS `Update_time`,
`CHECK_TIME` AS `Check_time`,
`TABLE_COLLATION` AS `Collation`,
`CHECKSUM` AS `Checksum`,
`CREATE_OPTIONS` AS `Create_options`,
`TABLE_COMMENT` AS `Comment`
FROM `information_schema`.`TABLES` t
WHERE BINARY `TABLE_SCHEMA`
IN ('database6')
ORDER BY Name ASC LIMIT 250 OFFSET 0;

# Time: 150303 19:52:35
# User@Host: database6[database6] @ [88.88.88.88]
# Thread_id: 5301921 Schema: database6 QC_hit: No
# Query_time: 5.578910 Lock_time: 0.000329 Rows_sent: 190 Rows_examined: 190
use database6;
SET timestamp=1425408755;
SHOW TABLE STATUS;

# Time: 150303 13:03:23
# User@Host: database6[database6] @ [88.88.88.88]
# Thread_id: 5125180 Schema: database6 QC_hit: No
# Query_time: 4.142104 Lock_time: 0.051231 Rows_sent: 445 Rows_examined: 451
use database6;
SET timestamp=1425384203;
SELECT c.`id_configuration`, c.`name`, cl.`id_lang`, cl.`value` cl_value, c.`value` c_value
FROM `ps_configuration` c
LEFT JOIN `ps_configuration_lang` cl ON (c.id_configuration = cl.id_configuration);

# Time: 150302 23:59:15
# User@Host: database6[database6] @ [88.88.88.88]
# Thread_id: 4905311 Schema: database6 QC_hit: No
# Query_time: 30.852557 Lock_time: 0.000134 Rows_sent: 190 Rows_examined: 190
use database6;
SET timestamp=1425338089;
SHOW TABLE STATUS;
# Time: 150303 0:15:02

# Time: 150302 23:59:15
# User@Host: database6[database6] @ [88.88.88.88]
# Thread_id: 4937630 Schema: database6 QC_hit: No
# Query_time: 7.089643 Lock_time: 0.000134 Rows_sent: 190 Rows_examined: 190
use database6;
SET timestamp=1425346452;
SHOW TABLE STATUS;
# Time: 150303 2:35:00

# Time: 150302 23:59:15
# User@Host: database6[database6] @ [88.88.88.88]
# Thread_id: 4934337 Schema: database6 QC_hit: No
# Query_time: 5.551792 Lock_time: 0.000193 Rows_sent: 190 Rows_examined: 190
use database6;
SET timestamp=1425345463;
SHOW TABLE STATUS;
# Time: 150303 2:18:57

# Time: 150302 23:59:15
# User@Host: database6[database6] @ [88.88.88.88]
# Thread_id: 4924180 Schema: database6 QC_hit: No
# Query_time: 5.229753 Lock_time: 0.000129 Rows_sent: 190 Rows_examined: 190
use database6;
SET timestamp=1425342742;
SHOW TABLE STATUS;

# Time: 150302 23:59:15
# User@Host: database6[database6] @ [88.88.88.88]
# Thread_id: 4913015 Schema: database6 QC_hit: No
# Query_time: 16.122583 Lock_time: 0.000089 Rows_sent: 190 Rows_examined: 190
use database6;
SET timestamp=1425340102;
SHOW TABLE STATUS;
# Time: 150303 0:48:30

# Time: 150302 23:59:15
# User@Host: database6[database6] @ [88.88.88.88]
# Thread_id: 4912095 Schema: database6 QC_hit: No
# Query_time: 13.453879 Lock_time: 0.000186 Rows_sent: 190 Rows_examined: 190
use database6;
SET timestamp=1425339827;
SHOW TABLE STATUS;
# Time: 150303 0:44:17

# Time: 150302 23:59:15
# User@Host: database6[database6] @ [88.88.88.88]
# Thread_id: 5024386 Schema: database6 QC_hit: No
# Query_time: 4.673667 Lock_time: 0.000514 Rows_sent: 12 Rows_examined: 353
use database6;
SET timestamp=1425369210;
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` manufacturer_name,
tl.`name` tax_name, t.`rate`, cl.`name` category_default, DATEDIFF(p.`date_add`, DATE_SUB(NOW(),
INTERVAL 20 DAY)) > 0 new
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` = 6)
LEFT JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 6)
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` = 6)
LEFT JOIN `ps_tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group` AND tr.`id_country` = 37 AND tr.`id_state` = 0)
LEFT JOIN `ps_tax` t ON (t.`id_tax` = tr.`id_tax`)
LEFT JOIN `ps_tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = 6)
LEFT JOIN `ps_manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
WHERE cp.`id_category` = 1 AND p.`active` = 1
ORDER BY cp.`position` ASC LIMIT 0,12;
# Time: 150303 8:54:38

# Time: 150302 23:59:15
# User@Host: database6[database6] @ [88.88.88.88]
# Thread_id: 5012315 Schema: database6 QC_hit: No
# Query_time: 5.853552 Lock_time: 0.000285 Rows_sent: 0 Rows_examined: 0
SET timestamp=1425367116;
INSERT INTO `ps_guest` (`id_operating_system`,`id_web_browser`,`id_customer`,`javascript`,`screen_resolution_x`,`screen_resolution_y`,`screen_color`,`sun_java`,`adobe_flash`,`adobe_director`,`apple_quicktime`,`real_player`,`windows_media`,`accept_language`) VALUES ('3','10','0','0','0','0','0','0','0','0','0','0','0','sk');
# Time: 150303 8:18:48

# Time: 150302 23:59:15
# User@Host: database6[database6] @ [88.88.88.88]
# Thread_id: 5012327 Schema: database6 QC_hit: No
# Query_time: 4.264233 Lock_time: 0.000115 Rows_sent: 1 Rows_examined: 2
use database6;
SET timestamp=1425367115;
SELECT p.`price`,
IFNULL((SELECT pa.price FROM `ps_product_attribute` pa WHERE id_product = 152 AND default_on = 1), 0) AS attribute_price,
p.`ecotax`

FROM `ps_product` p

WHERE p.`id_product` = 152 LIMIT 1;
# Time: 150303 8:18:36

# Time: 150302 23:59:15
# User@Host: database6[database6] @ [88.88.88.88]
# Thread_id: 4952280 Schema: database6 QC_hit: No
# Query_time: 7.832346 Lock_time: 0.000173 Rows_sent: 190 Rows_examined: 190
use database6;
SET timestamp=1425350965;
SHOW TABLE STATUS;
# Time: 150303 3:52:16

# Time: 150302 20:11:28
# User@Host: database6[database6] @ [88.88.88.88]
# Thread_id: 4802757 Schema: database6 QC_hit: No
# Query_time: 4.803710 Lock_time: 0.001821 Rows_sent: 12 Rows_examined: 353
use database6;
SET timestamp=1425323488;
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` manufacturer_name,
tl.`name` tax_name, t.`rate`, cl.`name` category_default, DATEDIFF(p.`date_add`, DATE_SUB(NOW(),
INTERVAL 20 DAY)) > 0 new
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` = 6)
LEFT JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 6)
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` = 6)
LEFT JOIN `ps_tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group` AND tr.`id_country` = 37 AND tr.`id_state` = 0)
LEFT JOIN `ps_tax` t ON (t.`id_tax` = tr.`id_tax`)
LEFT JOIN `ps_tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = 6)
LEFT JOIN `ps_manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
WHERE cp.`id_category` = 1 AND p.`active` = 1
ORDER BY cp.`position` ASC LIMIT 0,12;

# Time: 150302 18:03:18
# User@Host: database6[database6] @ [88.88.88.88]
# Thread_id: 4747689 Schema: database6 QC_hit: No
# Query_time: 4.946272 Lock_time: 0.000428 Rows_sent: 106 Rows_examined: 1553
use database6;
SET timestamp=1425315798;
SELECT p.*, p.id_category_default, pl.*, i.id_image, il.legend, m.name manufacturer_name,
DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL 20 DAY)) > 0 AS new
FROM `ps_category_product` cp
LEFT JOIN ps_category c ON (c.id_category = cp.id_category)
LEFT JOIN `ps_product` p ON p.`id_product` = cp.`id_product`

LEFT JOIN ps_product_lang pl ON (pl.id_product = p.id_product)
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 = 6)
LEFT JOIN ps_manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
WHERE p.`active` = 1 AND
c.nleft >= 2
AND c.nright <= 33
AND c.active = 1
AND pl.id_lang = 6
AND p.id_product IN (10,11,12,13,14,15,16,17,20,22,24,25,26,27,28,29,31,36,67,70,72,73,74,83,84,85,86,88,91,93,117,118,119,121,122,123,125,126,127,131,132,133,134,135,136,137,138,139,142,143,144,146,147,148,150,151,152,153,155,156,157,158,160,161,162,163,164,165,173,175,176,177,197,198,199,202,203,204,205,208,209,224,225,226,227,229,230,231,232,235,236,237,244,245,246,247,248,250,251,252,256,265,266,280,281,289) GROUP BY p.id_product ORDER BY cp.position asc LIMIT 0,200;

# Time: 150302 13:48:32
# User@Host: database6[database6] @ [88.88.88.88]
# Thread_id: 4639009 Schema: database6 QC_hit: No
# Query_time: 4.892094 Lock_time: 0.004084 Rows_sent: 12 Rows_examined: 353
use database6;
SET timestamp=1425300512;
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` manufacturer_name,
tl.`name` tax_name, t.`rate`, cl.`name` category_default, DATEDIFF(p.`date_add`, DATE_SUB(NOW(),
INTERVAL 20 DAY)) > 0 new
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` = 6)
LEFT JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 6)
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` = 6)
LEFT JOIN `ps_tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group` AND tr.`id_country` = 37 AND tr.`id_state` = 0)
LEFT JOIN `ps_tax` t ON (t.`id_tax` = tr.`id_tax`)
LEFT JOIN `ps_tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = 6)
LEFT JOIN `ps_manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
WHERE cp.`id_category` = 1 AND p.`active` = 1
ORDER BY cp.`position` ASC LIMIT 0,12;

# Time: 150302 10:50:07
# User@Host: database6[database6] @ [88.88.88.88]
# Thread_id: 4569759 Schema: database6 QC_hit: No
# Query_time: 5.411103 Lock_time: 0.000194 Rows_sent: 1 Rows_examined: 1
use database6;
SET timestamp=1425289807;
SELECT IF(COUNT(`id_product_attribute`), SUM(pa.`quantity`), p.`quantity`) total
FROM `ps_product` p
LEFT JOIN `ps_product_attribute` pa ON (pa.`id_product` = p.`id_product`)
WHERE p.`id_product` = 188
AND `id_product_attribute` = 1842
GROUP BY p.`id_product` LIMIT 1;