1
\$\begingroup\$

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){
  $selectSql_av = "
      `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`
  ";

  $fromSql_av = "
        `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`
  ";

  $whereSql_av_Marime = "
    `aL`.`idLang` = '".$GLOBALS['idLang']."' AND
    `avL`.`idLang` = '".$GLOBALS['idLang']."' AND
    `pv`.`id_produs` = '".$idProd."' AND
    `av`.`id_atribut` = '1'
  ";
  $resultS_av_Marimi = getContentMySql_2($selectSql_av, $fromSql_av, $whereSql_av_Marime, "", "");

  $whereSql_av_Culori = "
    `aL`.`idLang` = '".$GLOBALS['idLang']."' AND
    `avL`.`idLang` = '".$GLOBALS['idLang']."' AND
    `pv`.`id_produs` = '".$idProd."' AND
    `av`.`id_atribut` = '2'
  ";

  $resultS_av_CuloriResult = $selectSql_av . $fromSql_av . $whereSql_av_Culori . " GROUP BY `av`.`idGrupCuloare` ";

  $res = mysqli_query($GLOBALS['DBconnectLogin'], $resultS_av_CuloriResult);

  if (!$res || 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
The Show Warnings output

Using EXPLAIN EXTENDED and SHOW WARNINGS gives this:
SHOW WARNINGS and EXPLAIN EXTENDED

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.

\$\endgroup\$
  • \$\begingroup\$ Comments are not for extended discussion or full-on troubleshooting; this conversation has been moved to chat. I really appreciate the effort to make this question easier to review. Do note that optimizations suggested in comments should at least partly be in an answer instead. Thank you! \$\endgroup\$ – Vogel612 Jun 20 '18 at 11:49
  • \$\begingroup\$ this question appears to be off topic as there is an evidence in the question body that the query runs 200 times faster than claimed by the OP \$\endgroup\$ – Your Common Sense Jun 20 '18 at 12:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.