Re: MYSQL issues with query performance
This is great stuff thanks. How would i combine these two querys into one while maintaining its speed? I cut the query into half because i couldnt get it to work properly. This is the original query :
Showing rows 0 - 19 (20 total, Query took 2.3550 seconds.)
SELECT competences.name as compname FROM xref_users_competences
JOIN users ON users.id = xref_users_competences.users_id
JOIN xref_distance on xref_distance.postal2 = users.postal AND xref_distance.postal1 = '6663'
LEFT JOIN xref_users_searching on xref_users_competences.users_id = xref_users_searching.users_id
JOIN competences ON xref_users_competences.competences_id = competences.id WHERE xref_distance.distance < 92 AND xref_distance.distance > xref_users_competences.maxkm AND acceptsallworkgroups = '1' AND searching_or_offering = 'offering' AND xref_users_competences.deleted = 0 AND xref_users_competences.active = 1 AND xref_users_competences.deleted = '0' AND xref_users_competences.users_id != 1592824499514626 OR acceptsallworkgroups = "0" AND searching_or_offering = "offering" AND xref_users_competences.deleted = 0 AND xref_users_competences.active = "1" AND xref_users_competences.deleted = "0" and xref_users_searching.searching = "Searching" and xref_users_searching.competences_id IN (23) AND xref_distance.distance < 92 AND xref_distance.distance > xref_users_competences.maxkm AND xref_users_competences.users_id != 1592824499514626 ORDER BY average_rating DESC LIMIT 0,20
Now each query on its own (with ur help) is fast enough but when combining them using an union all it takes around 2.7 seconds. We are running MySQL 5.7 using a google cloud SQL instance. Currently we are testing on a machine with 1 vCPU and 3.75 GB RAM.
mysql> SELECT @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+------------------------------------------+
| 1.375000000000 |
+------------------------------------------+
1 row in set (0.01 sec)
mysql>
mysql> EXPLAIN SELECT xref_users_competences.users_id, users.average_rating, users.cities_id, xref_users_competences.id as xrefid, users.acceptsallworkgroups, users.email, users.id as user, cities_id, users.city as name, users.versionid_image,competences.parent_id as compid, competences.name as compname, xref_users_competences.competences_id, users.firstname, users.lastname, users.createdon, users.acceptsallworkgroups, users.profilepicture, competences.id, competences.name AS competence_name, xref_users_competences.maxkm, xref_users_competences.id AS comp, xref_users_competences.deleted, xref_users_competences.comments, xref_users_competences.functions, xref_users_competences.active, xref_distance.distance, xref_users_competences.searching_or_offering FROM xref_users_competences JOIN users ON users.id = xref_users_competences.users_id JOIN xref_distance on xref_distance.postal2 = users.postal AND xref_distance.postal1 = '6663' LEFT JOIN xref_users_searching onxref_users_competences.users_id = xref_users_searching.users_id JOIN competences ON xref_users_competences.competences_id = competences.id WHERE xref_distance.distance < 92 AND xref_distance.distance > xref_users_competences.maxkm AND acceptsallworkgroups = '1' AND searching_or_offering = 'offering' AND xref_users_competences.deleted = 0 AND xref_users_competences.active = 1 AND xref_users_competences.deleted = '0' AND xref_users_competences.users_id != 1592824499514626 OR acceptsallworkgroups = "0" AND searching_or_offering = "offering" AND xref_users_competences.deleted = 0 AND xref_users_competences.active = "1" AND xref_users_competences.deleted = "0" and xref_users_searching.searching = "Searching" and xref_users_searching.competences_id IN (23) AND xref_distance.distance < 92 AND xref_distance.distance > xref_users_competences.maxkm AND xref_users_competences.users_id != 1592824499514626 ORDER BY average_rating DESC LIMIT 0,20
-> ;
+----+-------------+------------------------+------------+--------+-------------------------------------+----------------------+---------+--------------------------------------------------+--------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------------+------------+--------+-------------------------------------+----------------------+---------+--------------------------------------------------+--------+----------+----------------------------------------------+
| 1 | SIMPLE | xref_users_competences | NULL | ALL | werkgroep_id,xref_users_competences | NULL | NULL | NULL | 338228 | 0.01 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | competences | NULL | eq_ref | PRIMARY,id,competences_idx_id | PRIMARY | 4 | gipaa_test.xref_users_competences.competences_id | 1 | 100.00 | NULL |
| 1 | SIMPLE | users | NULL | eq_ref | PRIMARY,users | PRIMARY | 8 | gipaa_test.xref_users_competences.users_id | 1 | 19.00 | Using where |
| 1 | SIMPLE | xref_distance | NULL | eq_ref | PRIMARY,xref_distance | PRIMARY | 28 | const,gipaa_test.users.postal | 1 | 20.98 | Using where |
| 1 | SIMPLE | xref_users_searching | NULL | ref | xref_users_searching | xref_users_searching | 8 | gipaa_test.xref_users_competences.users_id | 1 | 100.00 | Using where; Using index |
+----+-------------+------------------------+------------+--------+-------------------------------------+----------------------+---------+--------------------------------------------------+--------+----------+----------------------------------------------+
5 rows in set, 1 warning (0.01 sec)
The improved query:
mysql> EXPLAIN SELECT competences.name AS compname
-> FROM users
-> JOIN xref_users_competences ON xref_users_competences.users_id = users.id
-> AND xref_users_competences.users_id != 1592824499514626
-> LEFT JOIN xref_distance ON xref_distance.postal2 = users.postal
-> AND xref_distance.postal1 = '6663'
-> AND xref_distance.distance < 92
-> AND xref_distance.distance > xref_users_competences.maxkm
-> LEFT JOIN xref_users_searching ON xref_users_competences.users_id = xref_users_searching.users_id
-> AND xref_users_searching.searching = 'Searching'
-> AND xref_users_searching.competences_id IN (23)
-> JOIN competences ON xref_users_competences.competences_id = competences.id
-> AND xref_users_competences.deleted = 0
-> AND xref_users_competences.active = '1'
-> AND xref_users_competences.deleted = '0'
-> WHERE users.acceptsallworkgroups = '0';
+----+-------------+------------------------+------------+--------+-------------------------------------+----------------------+---------+--------------------------------------------------------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------------+------------+--------+-------------------------------------+----------------------+---------+--------------------------------------------------------+--------+----------+-------------+
| 1 | SIMPLE | xref_users_competences | NULL | ALL | werkgroep_id,xref_users_competences | NULL | NULL | NULL | 338228 | 0.05 | Using where |
| 1 | SIMPLE | competences | NULL | eq_ref | PRIMARY,id,competences_idx_id | PRIMARY | 4 | gipaa_test.xref_users_competences.competences_id | 1 | 100.00 | NULL |
| 1 | SIMPLE | users | NULL | eq_ref | PRIMARY,users | PRIMARY | 8 | gipaa_test.xref_users_competences.users_id | 1 | 10.00 | Using where |
| 1 | SIMPLE | xref_distance | NULL | eq_ref | PRIMARY,xref_distance | PRIMARY | 28 | const,gipaa_test.users.postal | 1 | 100.00 | Using where |
| 1 | SIMPLE | xref_users_searching | NULL | ref | xref_users_searching | xref_users_searching | 41 | gipaa_test.xref_users_competences.users_id,const,const | 1 | 100.00 | Using index |
+----+-------------+------------------------+------------+--------+-------------------------------------+----------------------+---------+--------------------------------------------------------+--------+----------+-------------+
5 rows in set, 1 warning (0.01 sec)
Improved query2:
mysql> Explain SELECT competences.name AS competence_name
-> FROM users
-> JOIN xref_users_competences ON xref_users_competences.users_id = users.id
-> AND xref_users_competences.deleted = 0
-> AND xref_users_competences.active = '1'
-> AND xref_users_competences.deleted = '0'
-> AND xref_users_competences.users_id != 1592824499514626
-> LEFT JOIN xref_distance ON xref_distance.postal2 = users.postal
-> AND xref_distance.postal1 = '6663'
-> AND xref_distance.distance < 92
-> AND xref_distance.distance > xref_users_competences.maxkm
-> JOIN competences ON xref_users_competences.competences_id = competences.id
-> WHERE users.acceptsallworkgroups = '1';
+----+-------------+------------------------+------------+--------+-------------------------------------+---------+---------+--------------------------------------------------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------------+------------+--------+-------------------------------------+---------+---------+--------------------------------------------------+--------+----------+-------------+
| 1 | SIMPLE | xref_users_competences | NULL | ALL | werkgroep_id,xref_users_competences | NULL | NULL | NULL | 338228 | 0.05 | Using where |
| 1 | SIMPLE | competences | NULL | eq_ref | PRIMARY,id,competences_idx_id | PRIMARY | 4 | gipaa_test.xref_users_competences.competences_id | 1 | 100.00 | NULL |
| 1 | SIMPLE | users | NULL | eq_ref | PRIMARY,users | PRIMARY | 8 | gipaa_test.xref_users_competences.users_id | 1 | 10.00 | Using where |
| 1 | SIMPLE | xref_distance | NULL | eq_ref | PRIMARY,xref_distance | PRIMARY | 28 | const,gipaa_test.users.postal | 1 | 100.00 | Using where |
+----+-------------+------------------------+------------+--------+-------------------------------------+---------+---------+--------------------------------------------------+--------+----------+-------------+
4 rows in set, 1 warning (0.01 sec)