Autor Zpráva
Virtus
Profil
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
A s pomínkou
AND (users_friends.ufr_id is not null OR galleries.g_private = 0 OR galleries.u_id = 204910 )
to zlobí také? Třeba je plánovač zmatený z ifnull v nečekaném použití. Případně můžete zkusit jinou (novější) verzi mysql, zda to je na všech a poslat jim report o chybě.

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ší :)

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm: