Autor Zpráva
Náprstek Aleš
Profil *
Dobrý den,

můžete mi prosím poradit, zda existuje nějaká možnost, jak zrychlit níže uvedený SELECT - používáme databázi MySQL 5.1. Select se zpomaluje s tím, jak přibývají počty řádků v dotyčné tabulce.
Jedná se o select, který vypisuje vždy určitý počet položek z vybrané katagorie a vybraného pořadí. Produkt může být v více kategorií a proto se používá vazební tabulka.

Nyní je počet řádků v tabulkách asi následující

t_e_kategorie 1345 řádků - tabulka kategorií produktů v eshopu
t_e_produkty 152 000 řádků - tabulka produktů v eshopu
t_e_vazba_kat_prod 167 000 řádků - vazební tabulka pro zařazení produktů do jednotlivých kategorií


Select uvádím níže (včetně explainu), poté je záznam z mysql slow logu a níže uvádím i strukturu tabulek. Select běží v součastnosti většinou kolem času 1,5 s.
Zkoušel jsem všechno možné a zatím se mi nepodařila select jakýmkoliv způsobem zoptimalizovat, aby byl rychlejší.

EXPLAIN SELECT kp.id_produkt AS id_produkt 
FROM t_e_vazba_katprod AS kp, t_e_produkty AS p 
WHERE p.zobrazovat = 'A' 
AND p.platny = 'A' 
AND kp.id_produkt = p.id_produkt 
AND id_e_kategorie = ANY(SELECT id_e_kategorie FROM t_e_kategorie WHERE lft >= 803 AND rft <= 814) 
ORDER BY p.d_zobrazovat_od DESC 
LIMIT 300, 15;

+----+--------------------+---------------+-----------------+-------------------------------+---------+---------+----------------------+--------+-----------------------------------------------------------+
| id | select_type        | table         | type            | possible_keys                 | key     | key_len | ref                  | rows   | Extra                                                     |
+----+--------------------+---------------+-----------------+-------------------------------+---------+---------+----------------------+--------+-----------------------------------------------------------+
|  1 | PRIMARY            | kp            | index           | vazba                         | vazba   | 10      | NULL                 | 172605 | Using where; Using index; Using temporary; Using filesort |
|  1 | PRIMARY            | p             | eq_ref          | PRIMARY,zobrazovat,idx_platny | PRIMARY | 4       | stosek.kp.id_produkt |      1 | Using where                                               |
|  2 | DEPENDENT SUBQUERY | t_e_kategorie | unique_subquery | PRIMARY,idx_lft,idx_rft       | PRIMARY | 2       | func                 |      1 | Using where                                               |
+----+--------------------+---------------+-----------------+-------------------------------+---------+---------+----------------------+--------+-----------------------------------------------------------+
3 rows in set (0.00 sec)


# Time: 150121 14:08:29
# Query_time: 1.726613  Lock_time: 0.000132 Rows_sent: 15  Rows_examined: 230015
SET timestamp=1421845709;
SELECT kp.id_produkt AS id_produkt FROM t_e_vazba_katprod AS kp, t_e_produkty AS p WHERE p.zobrazovat = 'A' AND p.platny = 'A' AND kp.id_produkt = p.id_produkt AND id_e_kategorie = ANY(SELECT id_e_kategorie FROM t_e_kategorie WHERE lft >= 803 AND rft <= 814) ORDER BY p.d_zobrazovat_od DESC LIMIT 300, 15;
# Time: 150121 14:08:33
# Query_time: 1.430588  Lock_time: 0.000142 Rows_sent: 15  Rows_examined: 181380
SET timestamp=1421845713;
SELECT kp.id_produkt AS id_produkt FROM t_e_vazba_katprod AS kp, t_e_produkty AS p WHERE p.zobrazovat = 'A' AND p.platny = 'A' AND kp.id_produkt = p.id_produkt AND id_e_kategorie = ANY(SELECT id_e_kategorie FROM t_e_kategorie WHERE lft >= 1785 AND rft <= 1786) ORDER BY p.d_zobrazovat_od DESC LIMIT 180, 15;
# Time: 150121 14:08:42
# Query_time: 1.614389  Lock_time: 0.000163 Rows_sent: 15  Rows_examined: 230047
SET timestamp=1421845722;
SELECT kp.id_produkt AS id_produkt FROM t_e_vazba_katprod AS kp, t_e_produkty AS p WHERE p.zobrazovat = 'A' AND p.platny = 'A' AND kp.id_produkt = p.id_produkt AND id_e_kategorie = ANY(SELECT id_e_kategorie FROM t_e_kategorie WHERE lft >= 803 AND rft <= 814) ORDER BY p.d_zobrazovat_od DESC LIMIT 315, 15;
# Time: 150121 14:08:55
# Query_time: 1.570270  Lock_time: 0.000154 Rows_sent: 15  Rows_examined: 230079
SET timestamp=1421845735;
SELECT kp.id_produkt AS id_produkt FROM t_e_vazba_katprod AS kp, t_e_produkty AS p WHERE p.zobrazovat = 'A' AND p.platny = 'A' AND kp.id_produkt = p.id_produkt AND id_e_kategorie = ANY(SELECT id_e_kategorie FROM t_e_kategorie WHERE lft >= 803 AND rft <= 814) ORDER BY p.d_zobrazovat_od DESC LIMIT 330, 15;



CREATE TABLE IF NOT EXISTS `t_e_kategorie` (
  `id_e_kategorie` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `nazev_cz` varchar(80) CHARACTER SET utf8 DEFAULT NULL,
  `popis_cz` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `nazev_en` varchar(80) CHARACTER SET utf8 DEFAULT NULL,
  `popis_en` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `nazev_de` varchar(80) CHARACTER SET utf8 DEFAULT NULL,
  `popis_de` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `id_e_kat_rodic` smallint(5) unsigned NOT NULL DEFAULT '0',
  `poradi` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `lft` smallint(5) unsigned NOT NULL DEFAULT '0',
  `rft` smallint(5) unsigned NOT NULL DEFAULT '0',
  `platny` enum('A','N') CHARACTER SET utf8 NOT NULL DEFAULT 'A',
  PRIMARY KEY (`id_e_kategorie`),
  KEY `idx_lft` (`lft`),
  KEY `idx_rft` (`rft`),
  KEY `idx_id_e_kat_rodic` (`id_e_kat_rodic`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

CREATE TABLE IF NOT EXISTS `t_e_produkty` (
  `id_produkt` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_kvalita` tinyint(3) unsigned DEFAULT NULL,
  `d_vlozeni` datetime DEFAULT NULL,
  `d_zobrazovat_od` datetime DEFAULT NULL, 
  `provize` tinyint(3) unsigned DEFAULT NULL,
  `katalogove_cislo` varchar(60) DEFAULT NULL,
  `nazev_cz` varchar(255) DEFAULT NULL,
  `nazev_en` varchar(255) DEFAULT NULL,
  `nazev_de` varchar(255) DEFAULT NULL,
  `popis_cz` text,
  `popis_en` text,
  `popis_de` text,
  `cena` int(10) DEFAULT NULL,
  `doporucujeme` enum('A','N') DEFAULT 'N',
  `d_doporucujeme` date DEFAULT NULL,
  `neprehlednete` enum('A','N') DEFAULT 'N',
  `pocet_zobrazeni` int(11) NOT NULL DEFAULT '0',
  `zobrazovat` enum('A','N') NOT NULL DEFAULT 'A',
  `platny` enum('A','N') DEFAULT 'A', 
  `vraceno` enum('A','N') DEFAULT 'N',
  `dt_vraceno` date DEFAULT NULL,
  PRIMARY KEY (`id_produkt`),
  KEY `idx_katalog` (`katalogove_cislo`),
  KEY `idx_cena` (`cena`),
  KEY `idx_doporucujeme` (`doporucujeme`),
  KEY `idx_nazev` (`nazev_cz`),
  KEY `zobrazovat` (`zobrazovat`),
  KEY `idx_neprehlednete` (`neprehlednete`),
  KEY `idx_platny` (`platny`),
  KEY `idx_d_zobrazovat_od` (`d_zobrazovat_od`),
  FULLTEXT KEY `idx_flt_nazev` (`nazev_cz`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `t_e_vazba_katprod` (
  `id_produkt` int(11) DEFAULT NULL,
  `id_e_kategorie` int(11) DEFAULT NULL,
  UNIQUE KEY `vazba` (`id_produkt`,`id_e_kategorie`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


Předem děkuji za jakoukoliv radu.
Aleš Náprstek
Keeehi
Profil
Tusím že join by mohl pomoct:
SELECT kp.id_produkt AS id_produkt 
FROM t_e_vazba_katprod AS kp INNER JOIN t_e_produkty AS p USING(id_produkt)
WHERE p.zobrazovat = 'A' 
AND p.platny = 'A' 
AND id_e_kategorie IN(SELECT id_e_kategorie FROM t_e_kategorie WHERE lft >= 803 AND rft <= 814) 
ORDER BY p.d_zobrazovat_od DESC 
LIMIT 300, 15;

Ta čárka, co jste tam měl předím, vytvoří kartézský součin kde se párují záznamy každý s každým což vytvoří tabulku o 25 miliardách řádků která se pak musí profiltrovat.


Ještě by se dalo zbavit toho vnořeného subselektu:
SELECT kp.id_produkt AS id_produkt 
FROM t_e_vazba_katprod AS kp INNER JOIN t_e_produkty AS p USING(id_produkt) INNER JOIN t_e_kategorie AS k USING(id_e_kategorie)
WHERE p.zobrazovat = 'A' 
AND p.platny = 'A' 
AND k.lft >= 803 
AND k.rft <= 814
ORDER BY p.d_zobrazovat_od DESC 
LIMIT 300, 15;
Kajman
Profil
A pokud se produkt má vypsat jen jednou (i když je ve více kategoriích), zkusil bych něco jako

SELECT p.id_produkt
FROM   t_e_produkty AS p
       INNER JOIN (SELECT DISTINCT kp.id_produkt
                   FROM   t_e_vazba_katprod AS kp
                          INNER JOIN t_e_kategorie AS k
                                  ON kp.id_e_kategorie = k.id_e_kategorie
                   WHERE  k.lft >= 803
                          AND k.rft <= 814) t
               ON p.id_produkt = t.id_produkt
WHERE  p.zobrazovat = 'A'
       AND p.platny = 'A'
ORDER  BY p.d_zobrazovat_od DESC
LIMIT  300, 15;

Pokud v p.zobrazovat a v p.platny jsou skoro vždy 'A', občas se v pomůže v mysql takovou podmínku dát do having místo do where, protože se lépe použije index pro řazení. Ale v tomto případě (závislost na dalších tabulkách) to asi nepomůže.

Edit: a v tabulce t_e_vazba_katprod by se hodilo přidat i obrácený index, tedy (id_e_kategorie,id_produkt)
Náprstek Aleš
Profil *
Děkuji moc za tip - zkusil jsem první variantu a ta přináší zatím úsporu v řádu desetin vteřin.
Chvíli nechám varinatu první a následně zkusím ještě druhou variantu s vynechám subselectem.


# Time: 150122 10:25:05
# Query_time: 1.847019  Lock_time: 0.000157 Rows_sent: 8  Rows_examined: 238735
SET timestamp=1421918705;
SELECT kp.id_produkt AS id_produkt FROM t_e_vazba_katprod AS kp INNER JOIN t_e_produkty AS p USING(id_produkt) WHERE p.zobrazovat = 'A' AND p.platny = 'A' AND kp.id_produkt = p.id_produkt AND id_e_kategorie = ANY(SELECT id_e_kategorie FROM t_e_kategorie WHERE lft >= 415 AND rft <= 416) ORDER BY p.d_zobrazovat_od DESC LIMIT 30, 30;
# Time: 150122 10:25:20
# Query_time: 1.312420  Lock_time: 0.000130 Rows_sent: 30  Rows_examined: 191522
SET timestamp=1421918720;
SELECT kp.id_produkt AS id_produkt FROM t_e_vazba_katprod AS kp INNER JOIN t_e_produkty AS p USING(id_produkt) WHERE p.zobrazovat = 'A' AND p.platny = 'A' AND kp.id_produkt = p.id_produkt AND id_e_kategorie = ANY(SELECT id_e_kategorie FROM t_e_kategorie WHERE lft >= 415 AND rft <= 416) ORDER BY p.d_zobrazovat_od DESC LIMIT 0, 30;
Náprstek Aleš
Profil *
Kajman:

Moc děkuji za optimalizaci selectu - podle měření na mém testovacím prostředí, to vypadá jako správná cesta. Za chvíli to vyzkouším nasadit na produkční server a dám vědět.
Stejně tak děkuji i za tip na obrácený index.
Náprstek Aleš
Profil *
Kajman&Keeehi:

Moc vám oběma děkuji, ten select #3 od Kajmana je nejrychlejší. Problém považuji za vyřešený, teď můžu pokračovat v řešení dalších pomalých sql příkazech.
Že já jsem nepoprosil o pomoc dříve - týden zkouším všechno možné i nemožné.

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm: