Ahoj, přesouvám phpbb fórum z jednoho serveru na druhý.
Původní mysql je 5.5.62 a dotaz zde trvá 0.00 sec, na serveru kam to přesouvám je 5.7.28 a dotaz běží 5 minut.
Indexy jsem ručně kontroloval a na všech 5 tabulkách jsou nastavené stejně, přesto podle explain proběhnou dotazy jinak.
Podle toho explainu jsem koukal na ty indexy, ale nic jsem nenašel.. Nepoznáte někdo pls jaký problém hledat? Díky
Sql dotaz:
SELECT ug.user_id, a.forum_id, r.auth_setting, r.auth_option_id, ao.auth_option
FROM phpbb3_acl_groups a, phpbb3_user_group ug, phpbb3_groups g, phpbb3_acl_roles_data r, phpbb3_acl_options ao
WHERE a.auth_role_id = r.role_id
AND r.auth_option_id = ao.auth_option_id
AND a.group_id = ug.group_id
AND g.group_id = ug.group_id
AND ug.user_pending = 0
AND NOT (ug.group_leader = 1 AND g.group_skip_auth = 1)
AND ao.auth_option = 'm_';
EXPLAIN MySQL 5.5.62
+----+-------------+-------+--------+-----------------------+--------------+---------+---------------------+-------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+--------+-----------------------+--------------+---------+---------------------+-------+----------+-------------+
| 1 | SIMPLE | ao | const | PRIMARY,auth_option | auth_option | 152 | const | 1 | 100.00 | |
| 1 | SIMPLE | r | ref | PRIMARY,ath_op_id | ath_op_id | 3 | const | 3 | 100.00 | |
| 1 | SIMPLE | a | ref | group_id,auth_role_id | auth_role_id | 3 | wareznet.r.role_id | 260 | 100.00 | |
| 1 | SIMPLE | g | eq_ref | PRIMARY | PRIMARY | 3 | wareznet.a.group_id | 1 | 100.00 | |
| 1 | SIMPLE | ug | ref | group_id,group_leader | group_id | 3 | wareznet.a.group_id | 25034 | 100.00 | Using where |
+----+-------------+-------+--------+-----------------------+--------------+---------+---------------------+-------+----------+-------------+
EXPLAIN MySQL 5.7.28
+----+-------------+-------+------------+--------+-----------------------+-------------+---------+-------------------------------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+-----------------------+-------------+---------+-------------------------------+-------+----------+-------------+
| 1 | SIMPLE | ao | NULL | const | PRIMARY,auth_option | auth_option | 152 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | g | NULL | ALL | PRIMARY | NULL | NULL | NULL | 13 | 100.00 | NULL |
| 1 | SIMPLE | ug | NULL | ref | group_id,group_leader | group_id | 3 | wareznet.g.group_id | 25034 | 10.00 | Using where |
| 1 | SIMPLE | a | NULL | ref | group_id,auth_role_id | group_id | 3 | wareznet.g.group_id | 240 | 100.00 | NULL |
| 1 | SIMPLE | r | NULL | eq_ref | PRIMARY,ath_op_id | PRIMARY | 6 | wareznet.a.auth_role_id,const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+-----------------------+-------------+---------+-------------------------------+-------+----------+-------------+
4 Odpovědí
2686 Zhlédnutí