Diskuze: MariaDB explain select type ALL

Petr Horáček

Člen

Petr Horáček:1.4.2022 16:45

Mám prosbu s vyladěním SQL dotazu nad MariaDB 10.5:

explain SELECT * FROM `tag_category` WHERE `tag_category`.`id` IN (SELECT `permission_memory`.`ref_id` FROM `permission_memory` WHERE (`permission_memory`.`ref_model`='app\\modules\\tag\\models\\TagCategory') AND (((`permission_memory`.`entity` IN ('owner', 'user')) AND (`permission_memory`.`entity_id`=72)) OR ((`permission_memory`.`entity` IN ('account', 'specialist')) AND (`permission_memory`.`entity_id` IN (-1, 33, 3, 752, -1, 33)))));

Výsledkem je:

id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       PRIMARY tag_category    ALL     PRIMARY NULL    NULL    NULL    4
1       PRIMARY <subquery2>     eq_ref  distinct_key    distinct_key    4       func    1
2       MATERIALIZED    permission_memory       range   idx_complex,idx_entity,idx_ref,idx_ref_model,idx_list_ref_model idx_complex     248     NULL    12      Using where; Using index

Je možné se nějak zbavit toho ALL v type prvního řádku? Alespoň na range, lépe pochopitelně na ref, eq_ref.

tag_category:

CREATE TABLE `tag_category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` int(11) DEFAULT NULL,
  `updated_at` int(11) DEFAULT NULL,
  `lock_version` bigint(20) DEFAULT 0,
  `name` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

permission_memory

CREATE TABLE `permission_memory` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` int(11) DEFAULT NULL,
  `updated_at` int(11) DEFAULT NULL,
  `entity` varchar(16) NOT NULL,
  `entity_id` int(11) NOT NULL,
  `ref_model` varchar(64) NOT NULL,
  `ref_id` int(11) NOT NULL,
  `sensitivity` varchar(1) DEFAULT NULL,
  `permissions` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_complex` (`entity`,`entity_id`,`ref_model`,`ref_id`),
  KEY `idx_entity` (`entity`,`entity_id`),
  KEY `idx_ref` (`ref_model`,`ref_id`),
  KEY `idx_ref_model` (`ref_model`),
  KEY `idx_list_ref_model` (`ref_model`,`entity`,`entity_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12517355 DEFAULT CHARSET=utf8;

Zkusil jsem: Zkusil jsem to přepsat na INNER JOIN, zbavím se 2. řádku subquery, který byl stejně eq_ref, ale na prvním je stále ALL:

explain SELECT * FROM `tag_category` inner join (SELECT `permission_memory`.`ref_id` FROM `permission_memory` WHERE (`permission_memory`.`ref_model`='app\\modules\\tag\\models\\TagCategory') AND (((`permission_memory`.`entity` IN ('owner', 'user')) AND (`permission_memory`.`entity_id`=72)) OR ((`permission_memory`.`entity` IN ('account', 'specialist')) AND (`permission_memory`.`entity_id` IN (-1, 33, 3, 752, -1, 33))))) as pm on `tag_category`.`id`=pm.ref_id;

id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  tag_category    ALL     PRIMARY NULL    NULL    NULL    4
1       SIMPLE  permission_memory       ref     idx_complex,idx_entity,idx_ref,idx_ref_model,idx_list_ref_model idx_ref 198     const,okis_live.tag_category.id 1       Using index condition; Using where

Zkoušel jsem i use index, use index for join, force index, ale bez úspěchu.

Chci docílit: Potřebuji vrátit hodnoty z tabulky tag_category podle oprávnění v permission_memory a chci dotaz optimalizovat tak, aby nebyl ALL.

Děkuji.

Odpovědět

1.4.2022 16:45

Peter Mlich

Člen

Peter Mlich:5.4.2022 9:12

1. Prosim te, jednoradkovymi dotazy se tu nikdo nebude zabyvat. Hezky to zformatuj, aby to bylo prehledne. Aspon pomoci https://www.dpriver.com/…qlformat.htm

SELECT *
FROM   `tag_category`
       INNER JOIN (SELECT `permission_memory`.`ref_id`
                   FROM   `permission_memory`
                   WHERE
       (
`permission_memory`.`ref_model` = 'app\\modules\\tag\\models\\TagCategory' )
        AND ( ( ( `permission_memory`.`entity` IN ( 'owner', 'user' ) )
                AND ( `permission_memory`.`entity_id` = 72 ) )
               OR ( ( `permission_memory`.`entity` IN
                      ( 'account', 'specialist' ) )
                    AND ( `permission_memory`.`entity_id` IN (
                          -1, 33, 3, 752,
                          -1, 33 ) ) ) ))
                                 AS pm
        ON `tag_category`.`id` = pm.ref_id

2. Dalsi vec, co si na tom vsimnes, proc tam davas INNER JOIN? Ten propoji vsechno se vsim. Pokud to nepotrebujes, tak se pouziva LEFT JOIN nebo RIGHT JOIN.

3. Proc to JOINujes tabulku pres SELECT? Je to nutne? Proc nepripojis tu tabulku primo?

LEFT JOIN `permission_memory` ON podminky

4. Proc tam mas tolik zavorek?

5. U podminek je mozna dobre to seradit tak, aby ty, ktere maji indexy a maji malou hodnotu (boolean, integer) byli pred temi, ktere resi stringy. Mysleno tak, ze, pokud je entity_id integer a entity je string, tak prvni bys mel kontrolovat asi integer a pak az string. Sporne je to, kdyz tam mas vycet pres IN. Tam zas asi bude rychlejsi porovnat nejdriv ty stringy. Ale, mozna to mas jako binarni typ. A jako, dlouhy string, ref model by mel byt asi na konci, ne? To by chtelo samozrejme zkusit, kolik to sezere casu.

FROM `tag_category` 't'
    LEFT JOIN `permission_memory` `p` ON
        p.`ref_id` = `t`.`id`
        AND (
               `p`.`entity` IN ( 'owner', 'user' ) -- tohle je binarni typ? pokud ne, na konec s nim
                AND `p`.`entity_id` = 72
                OR
                `p`.`entity` IN ( 'account', 'specialist' ) -- tohle je binarni typ? pokud ne, na konec s nim
                 AND `p`.`entity_id` IN (-1, 33, 3, 752,-1, 33)
                 )
        AND `p`.`ref_model` = 'app\\modules\\tag\\models\\TagCategory' --- tohle nevim, co je, ale stringy na konec

6. Nenapsal jsi zadnou cenu. Proc resit tve problemy, studovat tematiku, kdyz pak neuzivis s tim rodinu?

Nahoru Odpovědět

5.4.2022 9:12

Peter Mlich

Člen

Peter Mlich:5.4.2022 9:16

"Potřebuji vrátit hodnoty z tabulky tag_category podle oprávnění v permission_memory"
Jeste mne napada, A potrebujes, aby na vystupu byl seznam vsech tag_category? Pokud ne, mozna by slo skladat dotaz opacne. Primarni mit tabulku permission_memory a na ni left-joinovat. Opet, teoreticky by to melo byt rychlejsi a vrati to jen seznam, kam ma pristup

Nahoru Odpovědět

5.4.2022 9:16

Petr Horáček

Člen

Petr Horáček:7.4.2022 14:30

Ad 1 - děkuji, zkusím

Ad 2 - inner join proto, protože potřebuji odpovídající záznamy z obou; pokud použiju left/right, mám podle pořadí tabulek buď všechny z tag_category (nechci, neodpovídá permission) nebo permission_memory (nechci, jsou tam i permission k ostatním číselníkům)

Ad 3 - to jsem upravil, děkuji, aktuálně výsledný dotaz je:

SELECT tg.*
FROM   tag_category AS tg
       INNER JOIN permission_memory AS pm USE INDEX (idx_ref_id_ref_model)
               ON tg.id = pm.ref_id
                  AND pm.ref_model = 'app\\modules\\tag\\models\\TagCategory'
                  AND ( ( pm.entity IN ( 'owner', 'user' )
                          AND pm.entity_id = 72 )
                         OR ( pm.entity IN ( 'account', 'specialist' )
                              AND pm.entity_id IN ( -1, 33, 3, 752 ) ) )
ORDER  BY tg.name

S touto podobou se dostanu ale stále k ALL a filesort, nevím, jestli jsem vzhledem k tomu, že se z tbl_category vrací téměř celý obsah, dopracovat k něčemu lepšímu; pokud obrátím pořadí, mám i temporary, takže tento výsledek je pořád lepší

id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  tg      ALL     PRIMARY NULL    NULL    NULL    5       Using filesort
1       SIMPLE  pm      ref     idx_ref_id_ref_model    idx_ref_id_ref_model    198     okis_test.tg.id,const   1       Using index condition; Using where

Ad 4 - je to převzaté ze slow_logu a takto to do DB pošle framework

Ad 5 - problém je, že všechno jsou stringy - entity, ref_model

Ad 6 - nebyl na to textbox a u posledních dotazů ve fóru jsem nic podobného nenašel - pokud je potřeba, můžeme se domlouvat, nemám nic proti.

Nahoru Odpovědět

7.4.2022 14:30

Petr Horáček

Člen

Petr Horáček:7.4.2022 14:31

Viz předchozí Ad 2 - pokud použiji libovolnou podobu vnějšího joinu, vrátí mi to celou jednu nebo druhou tabulku.

Nahoru Odpovědět

7.4.2022 14:31

Petr Horáček

Člen

Petr Horáček:7.4.2022 20:35

Otázka pak je, jestli jít po tom, že type je ALL na tabulkách o maximálně 2-3 desítkách záznamů, nebo spíš omezit výskyt filesort, temptable, filetable.

Nahoru Odpovědět

7.4.2022 20:35

Peter Mlich

Člen

Peter Mlich:8.4.2022 7:48

Jak jsem psal. Jestli ti staci zjistit vsechny permition, tak bych si vytahl tabulku "permission_memory", na ni left-joinul tu druhou a pak to cele zgrupoval podle id (pokud tech permition je pro kategorii vice) a v select casti pouzil nejake group_concat, concat nebo tak neco.

Ale, jako, v zasade to nemas asi spatne. Jen bych prehazel poradi sloupcu pro WHERE, aby ty, co porovnavaji integer a sundaji nejvic radku se provedli jako prvni. Coz zase muze byt mozna i filtrovani podle slova do 6 znaku rychlejsi.

Hloupe je, ze tohle tam strkas jako string a neudelal sis k tomu tabulku pomocnou tabulku s id.
A totez entita, klidne mohla mit tabulku s id. Ona tam evidentne id ma, ale v tom dotazu ho nepouzijes pm.entity_id. Totiz, to by nevadilo, ale ty ten druhy select opakujes pro kazdy radek. Takze, on pokazde znovu a znovu zjistuje podle stringu radek s entitou

AND pm.ref_model = 'app\\modules\\tag\\models\\TagCategory'

pm.entity IN ( 'account', 'specialist' )

Nahoru Odpovědět

8.4.2022 7:48

Nejčastěji vyhledáváné

Diskuze: MariaDB explain select type ALL