| Autor | Zpráva | ||
|---|---|---|---|
| Náprstek Aleš Profil * |
#1 · Zasláno: 22. 1. 2015, 08:24:10
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 |
#2 · Zasláno: 22. 1. 2015, 08:53:32
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 * |
#4 · Zasláno: 22. 1. 2015, 10:28:01
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 * |
#5 · Zasláno: 22. 1. 2015, 10:33:47
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 * |
#6 · Zasláno: 22. 1. 2015, 14:07:35
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é. |
||
|
Časová prodleva: 11 let
|
|||
0