Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
11.3.2
-
None
-
2 servers :
1 pre-production
1 production
Description
Hello,
I have 2 servers (Preproduction and Production) with the same configuration (global variables)
Mariadb Version : 11.3 for both ser
I have a query that is being run on 2 servers :
Query Performance
preproduction environment
MariaDB [XXXX]> EXPLAIN EXTENDED SELECT users_field_data.uid AS uid
|
-> FROM users_field_data users_field_data
|
-> LEFT JOIN user__field_profil_nom user__field_profil_nom ON users_field_data.uid = user__field_profil_nom.entity_id
|
-> AND user__field_profil_nom.deleted = '0'
|
-> LEFT JOIN user__field_profil_prenom user__field_profil_prenom ON users_field_data.uid = user__field_profil_prenom.entity_id
|
-> AND user__field_profil_prenom.deleted = '0'
|
-> WHERE ((CONCAT_WS(' ', users_field_data.name, ' ', user__field_profil_nom.field_profil_nom_value, ' ', user__field_profil_prenom.field_profil_prenom_value, ' ', users_field_data.mail) LIKE '%XXXX%'))
|
-> AND (users_field_data.default_langcode = '1')
|
-> AND (users_field_data.uid != '0')
|
-> ORDER BY users_field_data.created DESC LIMIT 50
|
-> OFFSET 0;
|
|
|
|
|
+------+-------------+---------------------------+-------+----------------------------------------------+---------------------+---------+----------------------------------------------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+---------------------------+-------+----------------------------------------------+---------------------+---------+----------------------------------------------+------+----------+-------------+
|
| 1 | SIMPLE | users_field_data | index | PRIMARY,user__id__default_langcode__langcode | user_field__created | 4 | NULL | 50 | 100.00 | Using where |
|
| 1 | SIMPLE | user__field_profil_nom | ref | PRIMARY | PRIMARY | 5 | prep_cnas_preprod.users_field_data.uid,const | 1 | 100.00 | |
|
| 1 | SIMPLE | user__field_profil_prenom | ref | PRIMARY | PRIMARY | 5 | prep_cnas_preprod.users_field_data.uid,const | 1 | 100.00 | Using where |
|
+------+-------------+---------------------------+-------+----------------------------------------------+---------------------+---------+----------------------------------------------+------+----------+-------------+
|
3 rows in set, 1 warning (0,006 sec)
|
MariaDB [XXXX]> SELECT users_field_data.uid AS uid
|
-> FROM users_field_data users_field_data
|
-> LEFT JOIN user__field_profil_nom user__field_profil_nom ON users_field_data.uid = user__field_profil_nom.entity_id
|
-> AND user__field_profil_nom.deleted = '0'
|
-> LEFT JOIN user__field_profil_prenom user__field_profil_prenom ON users_field_data.uid = user__field_profil_prenom.entity_id
|
-> AND user__field_profil_prenom.deleted = '0'
|
-> WHERE ((CONCAT_WS(' ', users_field_data.name, ' ', user__field_profil_nom.field_profil_nom_value, ' ', user__field_profil_prenom.field_profil_prenom_value, ' ', users_field_data.mail) LIKE '%XXXX%'))
|
-> AND (users_field_data.default_langcode = '1')
|
-> AND (users_field_data.uid != '0')
|
-> ORDER BY users_field_data.created DESC LIMIT 50
|
-> OFFSET 0;
|
^[[28~+---------+
|
| uid |
|
+---------+
|
| 4654364 |
|
+---------+
|
1 row in set (3,669 sec)
|
production environment
MariaDB [prod_cnas_upg]> EXPLAIN SELECT users_field_data.uid AS uid
|
-> FROM users_field_data users_field_data
|
-> LEFT JOIN user__field_profil_nom user__field_profil_nom ON users_field_data.uid = user__field_profil_nom.entity_id
|
-> AND user__field_profil_nom.deleted = '0'
|
-> LEFT JOIN user__field_profil_prenom user__field_profil_prenom ON users_field_data.uid = user__field_profil_prenom.entity_id
|
-> AND user__field_profil_prenom.deleted = '0'
|
-> WHERE ((CONCAT_WS(' ', users_field_data.name, ' ', user__field_profil_nom.field_profil_nom_value, ' ', user__field_profil_prenom.field_profil_prenom_value, ' ', users_field_data.mail) LIKE '%8349005C%'))
|
-> AND (users_field_data.default_langcode = '1')
|
-> AND (users_field_data.uid != '0')
|
-> ORDER BY users_field_data.created DESC LIMIT 50
|
-> OFFSET 0;
|
+------+-------------+---------------------------+-------+----------------------------------------------+---------------------+---------+------------------------------------------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+---------------------------+-------+----------------------------------------------+---------------------+---------+------------------------------------------+------+-------------+
|
| 1 | SIMPLE | users_field_data | index | PRIMARY,user__id__default_langcode__langcode | user_field__created | 4 | NULL | 50 | Using where |
|
| 1 | SIMPLE | user__field_profil_nom | ref | PRIMARY | PRIMARY | 5 | prod_cnas_upg.users_field_data.uid,const | 1 | |
|
| 1 | SIMPLE | user__field_profil_prenom | ref | PRIMARY | PRIMARY | 5 | prod_cnas_upg.users_field_data.uid,const | 1 | Using where |
|
+------+-------------+---------------------------+-------+----------------------------------------------+---------------------+---------+------------------------------------------+------+-------------+
|
3 rows in set (0,001 sec)
|
MariaDB [XXXX]> SELECT users_field_data.uid AS uid FROM users_field_data users_field_data LEFT JOIN user__field_profil_nom user__field_profil_nom ON users_field_data.uid = user__field_profil_nom.entity_id AND user__field_profil_nom.deleted = '0' LEFT JOIN user__field_profil_prenom user__field_profil_prenom ON users_field_data.uid = user__field_profil_prenom.entity_id AND user__field_profil_prenom.deleted = '0' WHERE ((CONCAT_WS(' ', users_field_data.name, ' ', user__field_profil_nom.field_profil_nom_value, ' ', user__field_profil_prenom.field_profil_prenom_value, ' ', users_field_data.mail) LIKE '%XXXX%')) AND (users_field_data.default_langcode = '1') AND (users_field_data.uid != '0') ORDER BY users_field_data.created DESC LIMIT 50 OFFSET 0;
|
+---------+
|
| uid |
|
+---------+
|
| 4654364 |
|
+---------+
|
1 row in set (12,200 sec)
|
INDEX_STATISTICS on both servers
Preproduction
MariaDB [XXXX]> show index_statistics;
|
+-------------------+---------------------------+---------------------+-----------+
|
| Table_schema | Table_name | Index_name | Rows_read |
|
+-------------------+---------------------------+---------------------+-----------+
|
| XXXX | users_field_data | user_field__created | 576182 |
|
| XXXX | cachetags | PRIMARY | 984 |
|
| XXXX | user__field_profil_nom | PRIMARY | 576114 |
|
| XXXX | user__field_profil_prenom | PRIMARY | 566872 |
|
+-------------------+---------------------------+---------------------+-----------+
|
4 rows in set (0,000 sec)
|
Production
MariaDB [XXXX]> show index_statistics;
|
+---------------+---------------------------+---------------------+-----------+
|
| Table_schema | Table_name | Index_name | Rows_read |
|
+---------------+---------------------------+---------------------+-----------+
|
| XXXX | user__field_profil_prenom | PRIMARY | 945943 |
|
| XXXX | cache_container | PRIMARY | 10 |
|
| XXXX | user__field_profil_nom | PRIMARY | 957015 |
|
| XXXX | cachetags | PRIMARY | 240 |
|
| XXXX | users_field_data | user_field__created | 962750 |
|
+---------------+---------------------------+---------------------+-----------+
|
5 rows in set (0,000 sec)
|
summary
So basically, the query on preproduction is taking 3 seconds, but on the production environment it is taking 12 seconds.
The amount of data in the table are around 30K rows more on the production, but that , in my opinion shouldn't have such a big impact in terms of the number of seconds it takes to execute.
On both servers, i have same indexes, same collation.
King Regards,
Noor BEEGUM