Problem With Multishops And Configuration::loadconfiguration()

[a.karell]

Hi,

we recently discovered that with more shops we create, the sites were slower, so we profiled Prestashop discovering that a particular SQL consult is slowing the site more and more, I digged around and found that Configuration::loadConfiguration() is loading around 86k~ rows plus the language rows, this is the code:

$sql = 'SELECT c.`name`, cl.`id_lang`, IF(cl.`id_lang` IS NULL, c.`value`, cl.`value`) AS value, c.id_shop_group, c.id_shop
                FROM `'._DB_PREFIX_.bqSQL(self::$definition['table']).'` c
                LEFT JOIN `'._DB_PREFIX_.bqSQL(self::$definition['table']).'_lang` cl ON (c.`'.bqSQL(self::$definition['primary']).'` = cl.`'.bqSQL(self::$definition['primary']).'`)';

and that generates this:

select ...if(...) as value, ... from ps_configuration c left join ps_configuration_lang cl on (c.id_configuration = cl.id_configuration)

if we pay attention to cl the only index is id_configuration with id_language (is it using the index anyway?), it is not discriminating based on shop id or shop group or not loading shop specific ones, this is the stack:

 

 

as you can see, this is every request. Is this necessary?

 

Anyway, hope a dev can see this and help out, or if anyone has encountered this and fixed someway I would appreciate the insights.

 

I'm thinking to use slave for this but I think this is the most heavy query so it will slow down the slave. As second options I'm thinking of override get/set and loadconfiguration to use redis or mongodb or something like that, previously migrating all data and keep the data synced.

 

Cheers.

 

P.D. I tried to search in this forum anything relationated with "loadConfiguration" but there wasn't any related to this, and within all forum it was impossible to get any result that could suggest something like this.

Edited January 26, 2016 by a.karell (see edit history)