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 check 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
Why the execute plans are different?
Why the query is using the index in one of the nodes and not in the other ones?