Nette Database Explorer – Subquery ve FROM

libik

Ahoj,

snazim se (zatim marne) docilit pres NDB Selection dotazu:

SELECT
            o.*,
			`zakaznici`.*
FROM
    (SELECT
        `objednavky`.`id`
    FROM
        `objednavky`
        ORDER BY objednavky.`datum_vystaveni` ASC
        LIMIT 10
        ) as sorted
    JOIN objednavky o ON o.id = sorted.id
    LEFT JOIN `zakaznici` ON o.`zakaznici_id` = `zakaznici`.`id`
    GROUP BY o.`id`;

ale vysledek je

SELECT
    `objednavky`.*,
    `zakaznici`.*
FROM
    `objednavky`
        LEFT JOIN
    `zakaznici` ON `objednavky`.`zakaznici_id` = `zakaznici`.`id`
WHERE
    (`objednavky`.`id` IN (SELECT
            `id`
        FROM
            `objednavky`
        ORDER BY `objednavky`.`datum_vystaveni` DESC))
GROUP BY `objednavky`.`id`
ORDER BY `objednavky`.`datum_vystaveni` DESC;

Tedy ze subquery je ve WHERE, misto ve FROM, kde ho potrebuju. Dotaz je zkraceny jen pro nazornost. Obecne chci vybrat 10 zaznamu nejak serazenych a k nim hodnoty z joinovanych tabulek. V urcitych pripadech (razeni dle nekterych sloupcu) je stavajici dotaz pomaly ale se subquery ve FROM to leti jak po masle.

Lze toho nejak docilit? Dotaz nemuzu slozit rucne, potrebuju ‚fluent‘.

Diky.

MajklNajt

stačí pozrieť poriadne do dokumentácie https://doc.nette.org/…ase/explorer

„Jako parametr můžeme předat také výsledek z jiné tabulky – vytvoří se poddotaz:“

// WHERE `id` IN (SELECT `id` FROM `tableName`)
$table->where('id', $explorer->table($tableName));

// WHERE `id` IN (SELECT `col` FROM `tableName`)
$table->where('id', $explorer->table($tableName)->select('col'));

pokiaľ si to už skúšal a stále nefunguje, daj sem tvoj kód, bez kódu sa ťažko radí

mystik

Tohle pres Explorer pokud vim nejde.

Co presne ma ten dotaz delat? Proc je vubec potreba pouzit ten subquery. Potrebujes 10 poslednich objednavek, ale radit podle neceho jineho nez data objednavky? Radit to treba az na aplikacni urovni?

Dalsi otazka je proc by mela jedna verze byt rychlejsi. Pokud se spravne pouziji indexy nemel by v tom byt rozdil. Jake indexy mas na tabulce objednavky? Mohl bys udelat EXPLAIN nektere te pomale verze? Mozna by stacilo pridat nejaky index.

To ze v te druhe verzi chybi LIMIT je jen proto ze jsi to omylme smazal kdyz jsi to zjednodisoval?

libik

Jde o data pro dataTables.
Ta tabulka má spoustu sloupců, nechci index nad každým. Co jsem zkoušel, tak s odvozenou tabulkou je rychlost ze 60s na 1s, což by mi stačilo, protože řazení nad běžnými sloupci díky indexům funguje, ale pokud bych chtěl řadit podle méně běžných, tak se DB zapotí.

Ano LIMIT vypadl.

Zatím jsem Explorer nepřinutil k nějakému výsledku při pokusu použít odvozenou tabulku (https://dev.mysql.com/…-tables.html), tedy

SELECT ... FROM (subquery) [AS] tbl_name ...

Něco jako

$subQuery = $this->database->table('zakaznici')->select('id');
$outerQuery = $this->database->table($subQuery->getSql())->where...; - křičí, že obsah vložené SQL není název existující tabulky.

A obojí skládat přes selection.

Přes query napřímo to funguje

$subQuery = $this->database->table('objednavky')
                ->select('id')
                ->order('datum_vystaveni DESC')
                ->limit(10);

$outerQuery = $this->database->query('
    SELECT o.*
    FROM (?) AS sorted_orders
    JOIN objednavky o ON o.id = sorted_orders.id
', new \Nette\Database\SqlLiteral($subQuery->getSql()));

Ale potřebuju skládat outerQuery taky přes selection.

Tak mě akorát napadlo, že v DB by mohla být nějaká placeholder tabulka, nad kterou bych dotaz postavil, a před Fetch() bych si zavolal getSql() a placeholderovou tabulku ve FROM … bych nahradil tím subquery.

mystik

Nepotrebujes index nad kazdym. Ty potrebujes aby se vzdycky pouzil index nad datum_vystaveni ktery nejdriv omezi radky na tech 10 a vysledne reazeni uz je pak trivialni. Pripadne pri subdotazu ve FROM by se mel pouzit index pro id, vybrat 10 radku a pak az radit. Je potreba zjsitit proc se to nedeje a misto toho asi prbiha full scan.

Posli ty nastavene indexy a EXPLAIN pomaleho dotazu.

Mozna bude stacit jen pridat spravny USE INDEX.

Pokud by se to nepovedlo vyresit indexy asi bys sel cestou toho ze si udelas VIEW a budes selectovat z nej.

Jen presne teda vypada ten pomaly dotaz vcetne LIMIT a vseho?

Editoval mystik (10. 4. 9:44)

libik

Ona ta tabulka sama o sobě tak pomalá není, ale agreguji tam informace z dalších tabulek (pro jednoduchost jsem v příkladu neuváděl) a tam mi to smysl dává, abych měl nejprve vybráno v rámci limitu a seřazení několik záznamů z tabulky objednávky a pak v rámci outerQuery a přes cizí klíče k těm záznamům spojoval další informace. Protože jinak se to provádí pro všechny a tam vzniká ta časová nákladnost.

Subquery s odovozenou tabulkou mi to krásně vyřešil, a snažím se to teď aplikovat na Explorer.

mystik

Tohle ale ma resit execution planner databaze ze nejdriv vybere tech nekolik zaznamu a join dela uz jen s nimi. Vetsinou tyhle optimalizace vyresi lip nez ty. Pokud v tomhle pripade selhal a dela join celych tabulek je neco spatne. Zjistil bych proc nez zacnes pricinu obchazet tim ze skryjes symptomy.

libik

Tak vyřešeno. Jeden selection vybere na základě řazení nebo filtru X záznamů podle indexu a ty pošlu do druhého, kde se joinuje zbytek. Díky za konzultaci.