Dotaz: Pomalý SELECT

[dusan456]

Zdravím,

mám problém s rýchlosťou tohto dotazu:

SELECT A.name, A.id, B.popis, C.url, A.adresa, A.trieda 
FROM tab1 A, tab2 B, tab3 C 
WHERE A.top_id = '8930440' AND A.id=B.id AND A.id=C.id AND B.popistyp_id=11 
AND (B.language='sk' OR B.language='en') 
GROUP BY A.id
ORDER BY B.language='sk' DESC

A.id, B.id a C.id je index,

taktiež A.top_id je index,

B.language je index a

A.top_id je index.

B.popis typ je text.

B.popistyp_id nie je index, keďže mohutnosť je len 12, ale skúsil som dať aj index, no výsledok je rovnaký.

tab2 má okolo 3 mil. záznamov

Vykonanie dotazu trvá prvý krát niekoľko sekúnd, potom už ide rýchlo, čiže druhý, tretí atď to už nabehne okamžite. Ak ho však zadám po polhodine, zase prvý krát to trvá veľmi dlho, potom to už ide rýchlo.

Neviete prosím poradiť, ako by som to mohol zrýchliť?

A čo je vlastne príčinou toho, že prvý krát to ide pomaly a potom už rýchlo?

Vopred ďakujem za info.

Odpovědi

[AraxoN]

Skús EXPLAIN SELECT ... - to by Ti malo napovedať, či sa naozaj používajú indexy tam kde sa majú, alebo či ich treba vytvoriť / zmeniť / analyzovať (príkaz ANALYZE). Takto od pása by som ale tipoval, že pri prvom selecte sa to načítava z disku, preto to trvá dlhšie. Pri ďalších selectoch to potom už je v cache a ide to rádovo rýchlejšie. Ak sa to chvíľu nepoužíva, tak cache sa naplní inými vecami a zase sa to spomalí. Pomôže pridať do stroja viac RAM, ale ak sa jedná o naozaj veľkú databázu, tak potom už len optimalizovať aplikáciu.

[dusan456]

Ďakujem za info, dal som EXPAILN SELECT a dostal som tento výpis:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	         A 	ref 	top_id, id 	top_id 	4 	       const 	636 	Using temporary; Using filesort
1 	SIMPLE 	         C 	ref 	id 	        id 	4 	    test.A._id 	  1 	 
1 	SIMPLE 	         B 	ref 	id,language 	id 	4 	    test.A._id 	 35 	Using where

Takže v A tabulke mám použiť spoločný index pre top_id a id?

Ja používam index pre top_id a id na každé samostatne.

[AraxoN]

Skús taký index vytvoriť a cez EXPLAIN pozrieť, či ho potom použije. Ak ho nepoužije, tak ho opäť zhodíš - nemáš čo stratiť...

[vlasta]

ohledne te rychlosti, jeste muze byt problem v tom razeni, paklize ten dotaz vraci tisice radku, tak nejvic zdrzuje tohle. Zkus, jak to rychle dobehne bez toho razeni... Dalsi vec, co jsem nepochopil, je ten group by... K cemu to tam vlastne je a nezarve pritom interpret, ze se nejedna o group by vyraz?

[hikikomori82]

OR je problem. ak je tam, v podstate nemoze pouzit index. Skus to rozdelit unionom. Navyse, ake je rozlozenie v stlpci b.language? (select b.language, count(*) from b group by 1), ak je tam tych jazykov malo (2,3) tak taky index je nanic a aj tak pojde sekvencne. '8930440' je string? Ak nie pis to bez uvodzoviek.

[Šangala]

Jen poznámečka:
Pokud by se jednalo o MySQL, tak jsem měl zkušenost, že dotazy zapisované tímto způsobem (spojování tabulek přes WHERE) na velkých datech (statisíce až miliony řádku v několika tabulkách) bylo docela o dost pomalejší než použití klausule JOIN a potvrdilo se to i na M$SQL(už ale nevím verzi) i když tam byl rozdíl výrazně nižší.
Vysvětlil jsem si to tak, že při zápisu přes WHERE se vytvoří data na vše a pak se omezují a při JOINování dochází k postupné redukci dat, tudíž to nemá takové paměťové nároky.
A na Oracle (jen z konzultace, nezkoušel jsem to) je to prej jedno.

[dusan456]

..a nepomohol by ste mi prosim prerobiť to na JOIN dotaz? Ja som to stále nepochopil tie JOIN dotazy, ako to vlastne funguje.

[Šangala]

Zkusil jsem to - nějak se mně to nezdá a skoro spím :(
Pokud bych se na to měl podívat vážně, tak mně sem hoďte strukturu těch tabulek, potřeboval bych vědět kde jsou PRIMARNI a UNIQUE klíče a zítra během dopoledne se na to juknu (poctivě)

SELECT A.name, A.id, B.popis, C.url, A.adresa, A.trieda
  FROM tab3 AS C
    LEFT JOIN tab1 AS A ON A.id=C.id AND A.top_id = '8930440'
    LEFT JOIN tab2 AS B ON B.id=C.id AND B.popistyp_id=11
      WHERE (B.language='sk' OR B.language='en')
      GROUP BY A.id
        ORDER BY B.language='sk' DESC

[vlasta]

myslim, ze pouziti left joinu muze rapidne zmenit mnozinu vracenych radku... Nehlede na to, ze outer joiny jsou obecne pomalejsi...

[dusan456]

Pripájam štruktúru tabuliek a vopred ďakujem za Vašu ochotu.

Ešte som tu aj čítal, že InnoDB je rýchlejšia, ako MYISAM, možno aj to by pomohlo??

CREATE TABLE `tab3` (
  `id` int(16) NOT NULL default '0',
  `url` varchar(255) default NULL,
  KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `tab2` (
  `popis` text,
  `popistyp_id` int(6) NOT NULL default '0',
  `id` int(16) NOT NULL default '0',
  `language` char(2) default NULL,
  KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `tab1` (
  `adresa` varchar(255) default NULL,
  `top_id` int(16) NOT NULL default '0',
  `trieda` char(2) default NULL,
  `id` int(16) NOT NULL default '0',
  `name` varchar(255) default NULL,
  KEY `top_id` (`top_id`),
  KEY `id` (`id`),
  KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

[Šangala]

Tak s touto strukturou, je asi JOIN vs. WHERE putna, bo je to opravdu každý s každým :), čekal jsem tam nějaké UNIQUE či PRIMARY
Můj předchozí pokus s LEFT JOIN je špatně.

PS: to ORDER BY, má být opravdu na pravdivostní hodnotu ?, dal bych tam jen ORDER BY B.language DESC nebo ORDER BY B.language ASC

Takže asi takto:

SELECT DISTINCT A.name, A.id, B.popis, C.url, A.adresa, A.trieda
  FROM (SELECT * FROM tab1 WHERE top_id = '8930440') AS A
       INNER JOIN tab2 AS B ON A.id = B.id AND B.popistyp_id =11 AND B.language IN ('sk', 'en')
       INNER JOIN tab3 AS C ON A.id = C.id 
  ORDER BY B.language DESC

Čekal bych vyšší výkon ale nevím (no a pak ještě doladit indexi podle EXPLAIN).

[Šangala]

Ještě možná by pomohl jeden DISTINCT navíc.

SELECT DISTINCT A.name, A.id, B.popis, C.url, A.adresa, A.trieda
  FROM (SELECT DISTINCT name, id, adresa, trieda FROM tab1 WHERE top_id = '8930440') AS A
       INNER JOIN tab2 AS B ON A.id = B.id AND B.popistyp_id =11 AND B.language IN ('sk', 'en')
       INNER JOIN tab3 AS C ON A.id = C.id 
  ORDER BY B.language DESC

[vlasta]

Za predpokladu, ze id je v tab1 primarni klic, tak ten distinct ve vnitrnim selectu je naprosto zcestny, niceho tim nedosahnes, krom zpomaleni dotazu, coz je trosku v rozporu se zadanim.

A za hypotetickeho predpokladu, ze vazba mezi tabulkami 123 je 1:1, tak je k nicemu i ten prvni distinct.

A take nemuzu zapomenout na jeden dalsi fakt, ze MySQL pri pouziti nested selectu jaksi opomiji pouzit pri relaci index...

[Šangala]

"Za predpokladu, ze id je v tab1 primarni ani unique klic" NIC není primární klíč :).
A tím pádem vnitřní distinct omezuje množinu již na počátku.

[Šangala]

Sorry, repete:
"Za predpokladu, ze id je v tab1 primarni klic" — NIC není primární klíč :).
A tím pádem vnitřní distinct omezuje množinu již na počátku.

[vlasta]

poradil bych ti, at si koupis nejakou sql book for busy developers...

SELECT A.name, A.id, B.popis, C.url, A.adresa, A.trieda 
FROM tab1 A
JOIN tab2 B ON B.id = A.id
JOIN tab3 C ON C.id = A.id
WHERE A.top_id = 8930440 
AND B.popistyp_id=11 
--AND (B.language='sk' OR B.language='en') 
--hezci a jednoduseji modifikovatelna je podle me klauzule in
AND B.language IN ('sk', 'en')
GROUP BY A.id
ORDER BY B.language='sk' DESC

Jestli je klauzule join rychlejsi nez spojeni omezene v klauzuli where se rika, ze by to melo byt vykonove stejne (resp. parser by to mel chapat jako stejny vyraz) a jestli ne, tak se jedna o bug. Nicmene joiny jsou pro spoustu lidi prehlednejsi a v pripade jejich pouziti te syntakticka kontrola nepusti pres opomenute vyjadreni relace...

[Šangala]

S prohlášením, že se jedná o bug bych byl opatrný, to že výsledek je identický ještě neznamená, že vnitřní optimalizace nemůže postupovat jinak.
Ale pravděpodobně u INNER JOIN to tak bude. S provádením LEFT a RIGHT JOINU, už to ale bude jinak (nehledě na to že LEF a RIGHT mohou mít na některých enginech rozdílný výkon).

Pozn. na PostrgeSql, jsem četl, že rychlost přes WHERE a JOIN je za ideálních podmínek identická, což už samo o sobě vzbuzuje oprávněnou pochybnost a nutí to aspoň zkusit (bohužel to teď nemohu najít, abych dal odkaz).

[okbob]

Rychlost spojení zapsaného skreze WHERE a INNER JOINu musí být identická v každém případě (v PostgreSQL). Ideální podmínky jsou, s prominutím, blbost.

[okbob]

viz http://www.root.cz/clanky/leftright-outer-inner-join-v-sql/

[cronin]

Bez blizsieho skumania si dovolim podotknut, ze zabijakom vykonnosti je casto ORDER BY nad neindexovanym stlpcom. Ako uz bolo zmienene vyssie, tento ORDER BY obsahujuci hodnotu je sam o sebe podozrivy.

[dusan456]

Ďakujem všetkým za info, zistil som, že časový problém robí

ORDER BY language

15 rows in set (11.39 sec), bez ORDER BY je to (0.39 sec)

Potrebujem to však nutne vyriešiť, aby najprv radilo vysledky s language='sk' a až potom výsledky s language='en'.

Rozmyšlam vytvoriť tab4 v podstate identickú s tab1 v počte riadkov so stĺpcami 'id' a 'sk', kde by bolo 'sk' buď 1, alebo 0, podľa toho či obsahuje language='sk' a potom dať ORDER BY tab4.sk, možno to bude radiť rýchlejšie.

[cronin]

Nestaci urobit index nad language? Potom byt mozno vedela databaza sekvencne citat zaznamy z toho indexu, namiesto citania vsetkych a nasledneho triedenia. To je totiz uzitocny side-effect indexu, ze udaje v jeho listoch su zoradene. Takze ak treba ORDER BY, staci ist sekvencne podla indexu a testovat splnenie WHERE; vsetko sa ale da urobit streamovo. A ked sa k tomu pridaju selektivne indexy z PostgreSQL alebo FBI z Oracle, staci dobry index a netreba overovat ani to WHERE.

Ak sa skutocne jedna iba o pevne dany pocet jazykov, nie je mozne to vyriesit "inziniersky"? T.j. spustit dva selecty za sebou, kazdy pre jeden jazyk, a resultsety si spojit az v aplikacii?

[kulik]

To by snad musel byt bug databaze, setrideni 15 zaznamu by z hlediska casu vubec nemelo byt pozorovatelne. Jak se lisi plany s order by a bez order by? Je mozne, ze se voli plan, ktery by byl efektivni az od treba desetitisicu vybranych zaznamu. Pokud ano, pak je potreba ho zmenit, jak se to udela v MySQL nevim, ale pocitam ze nejake hinty by tam mely byt k dispozici.

[vlasta]

Resenim by bylo rozdelit dotaz na 2 totozne s rozdilnym language a spojit je pomoci UNION

[jakub hajek]

Na to pozor, na to, ze databaze vrati nejdriv zaznamy z prvni casti unionu a pak z druhe, nemuzete spolehat. Dokonce IMHO neni zaruceno ani to, ze zaznamy z jednotlivych casti unionu prijdou pohromade. Neznam tedy mysql, ale napr. v oracle tomu tak je.

[jos]

bez ORDER BY asi žádná databáze negarantuje pořadí vrácených záznamů, takže pozor na všechny SELECTy