trjhtr

I have an online shop on a custom PHP platform and I have some difficulties on optimising the color/size filters.
At the moment it takes 30 seconds to return the query.

You can test it here (the page load in 12-15 seconds and till 41-45 seconds it takes to return the filters using the following query)

Actual code:

foreach($prodIDs as $idProd) mysqli_num_rows($res) == 0)
 $final_array = FALSE;
 else 
 $final_array = array();

 while($row = mysqli_fetch_assoc($res)) 
 $final_array = $row;
 
 

 return $final_array;

Variable $prodIDs is an array containing all product id's to get it's childrens that gave filters (color/size).

The actual idea is:

Main product
 Child product with color black and size 38
 Child product with color black and size 40
 Child product with color white and size 40
 [...]

The query:

SELECT
`pv`.`id`,
`pva`.`id_valoare`,
`av`.`idGrupCuloare`,
`av`.`id_atribut`,
`av`.`codCuloare`,
`av`.`img`,
`av`.`sort`,
`gc`.`nume` as `gc_nume`,
`gc`.`codCuloare` as `gc_codCuloare`,
`aL`.`nume` as `aL_nume`,
`avL`.`nume` as `avL_nume`

FROM
`produse_variante` `pv`
JOIN `produse_variante_atribute` `pva` ON `pva`.`id_varianta` = `pv`.`id`
JOIN `atribute_valori` `av` ON `av`.`id` = `pva`.`id_valoare`
JOIN `atribute_lang` `aL` ON `av`.`id_atribut` = `aL`.`idAtribut`
JOIN `atribute_valori_lang` `avL` ON `pva`.`id_valoare` = `avL`.`idAtributValori`
LEFT JOIN `grup_culori` `gc` ON `av`.`idGrupCuloare` = `gc`.`id`

WHERE
`aL`.`idLang` = '1' AND
`avL`.`idLang` = '1' AND
`pv`.`id_produs` = '5218' AND
`av`.`id_atribut` = '2'

GROUP BY `av`.`idGrupCuloare`

The query's EXPLAIN EXTENDED:

Array
(
 [0] => Array
 (
 [id] => 1
 [select_type] => SIMPLE
 [table] => aL
 [type] => ref
 [possible_keys] => idAtribut,idLang
 [key] => idLang
 [key_len] => 1
 [ref] => const
 [rows] => 1
 [filtered] => 100.00
 [Extra] => Using where; Using temporary; Using filesort
 )

 [1] => Array
 (
 [id] => 1
 [select_type] => SIMPLE
 [table] => av
 [type] => ALL
 [possible_keys] => PRIMARY,id_atribut,idGrupCuloare
 [key] => 
 [key_len] => 
 [ref] => 
 [rows] => 375
 [filtered] => 93.33
 [Extra] => Using where; Using join buffer (Block Nested Loop)
 )

 [2] => Array
 (
 [id] => 1
 [select_type] => SIMPLE
 [table] => gc
 [type] => eq_ref
 [possible_keys] => PRIMARY
 [key] => PRIMARY
 [key_len] => 4
 [ref] => database.av.idGrupCuloare
 [rows] => 1
 [filtered] => 100.00
 [Extra] => 
 )

 [3] => Array
 (
 [id] => 1
 [select_type] => SIMPLE
 [table] => avL
 [type] => ref
 [possible_keys] => idAtributValori
 [key] => idAtributValori
 [key_len] => 4
 [ref] => database.av.id
 [rows] => 2
 [filtered] => 100.00
 [Extra] => Using where
 )

 [4] => Array
 (
 [id] => 1
 [select_type] => SIMPLE
 [table] => pv
 [type] => ref
 [possible_keys] => PRIMARY,id_produs,id
 [key] => id_produs
 [key_len] => 4
 [ref] => const
 [rows] => 22
 [filtered] => 100.00
 [Extra] => 
 )

 [5] => Array
 (
 [id] => 1
 [select_type] => SIMPLE
 [table] => pva
 [type] => eq_ref
 [possible_keys] => id,id_valoare
 [key] => id
 [key_len] => 8
 [ref] => database.pv.id,database.av.id
 [rows] => 1
 [filtered] => 100.00
 [Extra] => Using index
 )
)

This is the SHOW WARNINGS statement return:

Array ( [0] => Array ( [@@session.warning_count] => 0 ) )

I used the following code immediately after the query for the color filter:

$show_warnings = "SHOW COUNT(*) WARNINGS";
$res = mysqli_query($GLOBALS['DBconnectLogin'], $sql);

if (!$res || mysqli_num_rows($res) == 0)
 $array = FALSE;
 else 
 $array = array();
 while($row = mysqli_fetch_assoc($res)) 
 $array = $row;
 

I have also run the queries in question in the MySql console:

SHOW WARNINGS after query

Using EXPLAIN EXTENDED and SHOW WARNINGS gives this:

DB structure:

CREATE TABLE `produse_variante` (
 `id` int(11) NOT NULL,
 `id_produs` int(11) NOT NULL,
 `stoc` int(11) NOT NULL,
 `pret` decimal(7,2) NOT NULL,
 `pretVechi` decimal(7,2) NOT NULL,
 `pretDeBaza` decimal(7,2) NOT NULL,
 `codProdus` char(100) NOT NULL COMMENT 'se generea automat dupa codProdus2, se ignora _Marimea, folositor in front la afisarea produselor',
 `codProdus2` varchar(100) NOT NULL,
 `barcode` char(50) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
 `cantitateGrBrut` mediumint(9) NOT NULL,
 `idUserEdit` int(11) NOT NULL,
 `data_modificarii` datetime NOT NULL,
 `implicit` enum('0','1') NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

id -> primary key AND index
stoc, pret, codProdus2, id_produs, codProdus -> indexes

/************************************/

CREATE TABLE `produse_variante_atribute` (
 `id_varianta` int(11) NOT NULL,
 `id_valoare` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=FIXED;

id_varianta, id_valoare -> indexes

/************************************/

CREATE TABLE `atribute_valori` (
 `id` int(11) NOT NULL,
 `id_atribut` int(11) NOT NULL,
 `idGrupCuloare` int(11) NOT NULL,
 `codCuloare` char(30) COLLATE latin1_general_ci NOT NULL,
 `img` char(100) COLLATE latin1_general_ci NOT NULL,
 `sort` mediumint(9) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=FIXED;

id -> primary key
id_atribut, idGrupCuloare -> indexes

/************************************/

CREATE TABLE `atribute_lang` (
 `id` int(11) NOT NULL,
 `idAtribut` int(11) NOT NULL,
 `idLang` tinyint(4) NOT NULL,
 `nume` char(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED;

id -> primary key
idAtribut, idLang -> indexes

/************************************/

CREATE TABLE `atribute_valori_lang` (
 `id` int(11) NOT NULL,
 `idAtributValori` int(11) NOT NULL,
 `idLang` tinyint(4) NOT NULL,
 `nume` char(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED;

id -> primary key
idAtributValori -> indexes

/************************************/

CREATE TABLE `grup_culori` (
 `id` int(11) NOT NULL,
 `nume` varchar(50) NOT NULL,
 `codCuloare` char(30) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

id -> primary key
- no indexes

I've put after each table the indexes too

Problem:
It seems that this query takes a lot of time and I cant figure out why.
Without using this query, the page above specified as test page is fully loaded in max 15 seconds (still bad, but that's another issue - consider it out of subject)
Using the network test of chrome developer tools I can see the load time without this query at max 15s and with its like 42-43s.

Question:

Am I wrong somewhere?
Can this be optimised?

I've included the query fetch only for the color filter because that is the problematic one.

The size filter works ok I can say... it takes only 2 seconds.