| Autor | Zpráva | ||
|---|---|---|---|
| Virtus Profil |
#1 · Zasláno: 20. 2. 2014, 14:29:15 · Upravil/a: Virtus
Dobrý den, potřeboval bych vysvětlit chování následujícího subselectu, respektive vysvětlit proč samotný vnořený dotaz ovlivňuje pouze 9146 řádků v tabulce galerií a poté co je použit jako vnořený se tabulka gallerií připojí celá. Děkuji za odpovědi.
select min( aux.g_published ) min
from (
select
galleries.g_published
from
galleries
left join users_friends on users_friends.u_id_friend = 204910 and galleries.g_private = 2 and users_friends.u_id = galleries.u_id
where
galleries.g_enabled = 1 and
galleries.g_forum_enabled = 1 and
ifnull( users_friends.ufr_id, ( galleries.g_private = 0 or galleries.u_id = 204910 ) )
order by galleries.g_published desc, galleries.g_id desc limit 31
) aux +----+-------------+---------------+--------+------------------------------+-----------------+---------+--------------------+-------+----------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------+--------+------------------------------+-----------------+---------+--------------------+-------+----------+-----------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 31 | 100.00 | | | 2 | DERIVED | galleries | ALL | enabled,g_forum_enabled | g_forum_enabled | 1 | | 51189 | 17.87 | Using where; Using filesort | | 2 | DERIVED | users_friends | eq_ref | friend_pair,u_id,u_id_friend | friend_pair | 8 | ast.galleries.u_id | 1 | 100.00 | Using where | +----+-------------+---------------+--------+------------------------------+-----------------+---------+--------------------+-------+----------+-----------------------------+ Samotný subselect: select
galleries.g_published
from
galleries
left join users_friends on users_friends.u_id_friend = 204910 and galleries.g_private = 2 and users_friends.u_id = galleries.u_id
where
galleries.g_enabled = 1 and
galleries.g_forum_enabled = 1 and
ifnull( users_friends.ufr_id, ( galleries.g_private = 0 or galleries.u_id = 204910 ) )
order by galleries.g_published desc, galleries.g_id desc limit 31+----+-------------+---------------+--------+------------------------------+-----------------+---------+--------------------------+------+----------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------+--------+------------------------------+-----------------+---------+--------------------------+------+----------+-----------------------------+ | 1 | SIMPLE | galleries | ref | enabled,g_forum_enabled | g_forum_enabled | 1 | const | 9146 | 100.00 | Using where; Using filesort | | 1 | SIMPLE | users_friends | eq_ref | friend_pair,u_id,u_id_friend | friend_pair | 8 | ast.galleries.u_id,const | 1 | 100.00 | Using where | +----+-------------+---------------+--------+------------------------------+-----------------+---------+--------------------------+------+----------+-----------------------------+ Tak je očividně problém v podmínce: ifnull( users_friends.ufr_id, ( galleries.g_private = 0 or galleries.u_id = 204910 ) ) dotaz přepsán na: select min( aux.g_published ) min
from (
select galleries.g_published g_published, galleries.g_id g_id
from galleries
where galleries.g_enabled = 1 and
galleries.g_forum_enabled = 1 and
galleries.g_private = 0
union DISTINCT
select galleries.g_published g_published, galleries.g_id g_id
from galleries
where galleries.g_enabled = 1 and
galleries.g_forum_enabled = 1 and
galleries.u_id = 204910
union DISTINCT
select galleries.g_published g_published, galleries.g_id g_id
from galleries
inner join users_friends on users_friends.u_id_friend = 204910 and galleries.g_private = 2 and users_friends.u_id = galleries.u_id
where galleries.g_enabled = 1 and
galleries.g_forum_enabled = 1
order by g_published desc, g_id desc limit 31
) aux+----+--------------+---------------+------+--------------------------------------+-----------------+---------+------------------------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+---------------+------+--------------------------------------+-----------------+---------+------------------------+------+----------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 31 | | | 2 | DERIVED | galleries | ref | enabled,g_forum_enabled | g_forum_enabled | 1 | | 9146 | Using where | | 3 | UNION | galleries | ref | fk_u_id,enabled,g_forum_enabled,u_id | fk_u_id | 4 | | 1 | Using where | | 4 | UNION | users_friends | ref | friend_pair,u_id,u_id_friend | u_id_friend | 4 | | 1 | | | 4 | UNION | galleries | ref | fk_u_id,enabled,g_forum_enabled,u_id | u_id | 5 | ast.users_friends.u_id | 2 | Using where | | NULL | UNION RESULT | <union2,3,4> | ALL | NULL | NULL | NULL | NULL | NULL | Using filesort | +----+--------------+---------------+------+--------------------------------------+-----------------+---------+------------------------+------+----------------+ Přesto by mně stále zajímalo proč nedokáže mysql použít vrácená data ze subselectu a musí tabulku galerií připojit celou v[#1] Virtus |
||
| Kajman Profil |
#2 · Zasláno: 20. 2. 2014, 15:10:07
A s pomínkou
AND (users_friends.ufr_id is not null OR galleries.g_private = 0 OR galleries.u_id = 204910 ) A je jen jiný explain nebo se výrazně mění i čas provádění? |
||
| Virtus Profil |
Tak sem se svojí strohou angličtinou narazil na tohle: https://dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html a úplně dole na stránce:
citace: " The use of triggered conditions has some performance implications. A NULL IN (SELECT ...) expression now may cause a full table scan (which is slow) when it previously did not. This is the price paid for correct results (the goal of the trigger-condition strategy was to improve compliance and not speed). " Rozdíl v rychlosti je zrhuba 0.02s. Ještě sou na tabulce špatný indexy, takže věřím, že po optimalizaci indexů bude výsledek ještě o něco rychlejší. Edit: [#2] Kajman s AND (users_friends.ufr_id is not null OR galleries.g_private = 0 OR galleries.u_id = 204910 ) byl explain stejný jako s IFNULL
|
||
| Virtus Profil |
Ještě bych dodal, že dotaz sám o sobě z [#1] byl rychlejší s uniony, ale vzhledem k tomu, že to je jen kousek z mnohem většího dotazu, výsledek byl nakonec naprosto stejný a to i po optimalizaci indexů, díky kterým se z tabulky galerií vybírá podle explain jenom 5368 řádků, problém je v tom, že buď se řadí celá tabulka galerií a do temp tabulky se odesílá již jenom dle limitu 31 záznamů a nebo se do temp tabulky odesílá 5368 záznamů a ty se následně seřadí v temp tabulce.
výňatek z profileru nad celou query, ne jen části z [#1]: Dotaz s uniony: +----------------------+----------+ | Status | Duration | +----------------------+----------+ | Sending data | 0.013356 | | Sorting result | 0.000518 | bez uniunů: | Sorting result | 0.011002 | | Sending data | 0.000196 | Závěrem tedy, pokud by se omezil výsledek vybraných řádků z tabulky galerií, určitě by byl rychlejší dotaz s uniony v celkovým výsledku. Pokud tedy nebude výsledek dostatečně omezen nemá cenu dotaz předělávat, přece jenom bez unionů, to je o něco kratší :) |
||
|
Časová prodleva: 10 let
|
|||
0