I have a problem with this query:
SELECT uca.user_activity_id, uca.user_call_id, uca.call_activity_id, uca.user_activity_token, uc.call_group_id, uc.user_id FROM users_calls_activities uca INNER JOIN users_calls_activities uca2 ON uca2.user_activity_id = uca.user_activity_is_validated_with AND aux.user_call_id = 1744136 INNER JOIN users_calls uc ON uc.user_call_id = uca.user_call_id; We have a cluster with percona server (5.6.29) with 5 nodes(from 0 to 4) in Azure. The difference between nodes 0-3 and 4 is that, the first ones are in a balancer and the node 4 is out of the balancer (but in the cluster)
The problem is that in four of the servers (nodes 0-3) the query is really slow (15 sec) and in the other one (node 4)the query is really fast (0,002)
Afaik, the explain plan should be the same but i execute an EXPLAIN and the result is this:
Nodes 0-3 (Slow)
+----+-------------+-------+------+-------------------------------------------------------------+--------------+---------+-------------------------------+---------+---------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-------------------------------------------------------------+--------------+---------+-------------------------------+---------+---------------------------------------+ | 1 | SIMPLE | uca2 | ref | PRIMARY,user_call_id,user_call_id_2 | user_call_id | 4 | const | 1 | Using index | | 1 | SIMPLE | uc | ALL | PRIMARY,user_call_id | NULL | NULL | NULL | 2098152 | Using join buffer (Block Nested Loop) | | 1 | SIMPLE | uca | ref | user_call_id,user_call_id_2,is_validated_with | user_call_id | 4 | db.uc.user_call_id | 1 | Using where | +----+-------------+-------+------+-------------------------------------------------------------+--------------+---------+-------------------------------+---------+---------------------------------------+
Node 4 (Fast)
+----+-------------+-------+--------+-------------------------------------------------------------+---------------------------------+---------+-----------------------------------+---------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+-------------------------------------------------------------+---------------------------------+---------+-----------------------------------+---------+-----------------------+ | 1 | SIMPLE | uca2 | ref | PRIMARY,user_call_id,user_call_id_2 | user_call_id | 4 | const | 1 | Using index | | 1 | SIMPLE | uca | ref | user_call_id,user_call_id_2,is_validated_with | is_validated_with | 5 | db.uc2.user_activity_id | 2755595 | Using index condition | | 1 | SIMPLE | uc | eq_ref | PRIMARY,user_call_id | PRIMARY | 4 | db.uca.user_call_id | 1 | NULL | +----+-------------+-------+--------+-------------------------------------------------------------+---------------------------------+---------+-----------------------------------+---------+-----------------------+
I notice that in the slow one the index is not being used. so i checked the indexes:
Node 0:
+-----------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | users_calls | 0 | PRIMARY | 1 | user_call_id | A | 2099153 | NULL | NULL | | BTREE | | | | users_calls | 1 | call_group_id | 1 | call_group_id | A | 16659 | NULL | NULL | | BTREE | | | | users_calls | 1 | user_call_begin_date | 1 | user_call_begin_date | A | 1049576 | NULL | NULL | YES | BTREE | | | | users_calls | 1 | user_call_begin_date | 2 | user_call_end_date | A | 2099153 | NULL | NULL | YES | BTREE | | | | users_calls | 1 | user_call_id | 1 | user_call_id | A | 2099153 | NULL | NULL | | BTREE | | | | users_calls | 1 | user_call_id | 2 | user_id | A | 2099153 | NULL | NULL | | BTREE | | | | users_calls | 1 | user_id | 1 | user_id | A | 91267 | NULL | NULL | | BTREE | | | | users_calls | 1 | user_id | 2 | call_id | A | 2099153 | NULL | NULL | | BTREE | | | | users_calls | 1 | user_id | 3 | user_call_status | A | 2099153 | NULL | NULL | | BTREE | | | | users_calls | 1 | fk_users_calls_calls | 1 | call_id | A | 23067 | NULL | NULL | | BTREE | | | +-----------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
Node 4:
+-----------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | users_calls | 0 | PRIMARY | 1 | user_call_id | A | 2091476 | NULL | NULL | | BTREE | | | | users_calls | 1 | call_group_id | 1 | call_group_id | A | 26813 | NULL | NULL | | BTREE | | | | users_calls | 1 | user_call_begin_date | 1 | user_call_begin_date | A | 1045738 | NULL | NULL | YES | BTREE | | | | users_calls | 1 | user_call_begin_date | 2 | user_call_end_date | A | 2091476 | NULL | NULL | YES | BTREE | | | | users_calls | 1 | user_call_id | 1 | user_call_id | A | 2091476 | NULL | NULL | | BTREE | | | | users_calls | 1 | user_call_id | 2 | user_id | A | 2091476 | NULL | NULL | | BTREE | | | | users_calls | 1 | user_id | 1 | user_id | A | 53627 | NULL | NULL | | BTREE | | | | users_calls | 1 | user_id | 2 | call_id | A | 2091476 | NULL | NULL | | BTREE | | | | users_calls | 1 | user_id | 3 | user_call_status | A | 2091476 | NULL | NULL | | BTREE | | | | users_calls | 1 | fk_users_calls_calls | 1 | call_id | A | 15608 | NULL | NULL | | BTREE | | | +-----------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
My first question is why are the indexes different? it should be the same due to both are in the same cluster right?
Why the execute plans are different? Both are in the same cluster so, it should be the same
Should i use FORCE INDEX or STRAIGHT_JOIN?
0 comments:
Post a Comment