MySQL Forums
Forum List  »  Performance

Re: MYSQL issues with query performance
Posted by: Wouter Platteeuw
Date: June 30, 2020 06:13AM

Thanks for you're patience with me :)

Sorry forget everything i said before its a confusing shitty query. Lets try and simplify it a bit. I am removing some of the parameters in WHERE clause. (The filters i left out are for both sides of the OR statement the same)

(60000 records, Query took 0.8894 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
users.acceptsallworkgroups = '1' OR
users.acceptsallworkgroups = '0' AND
xref_users_searching.searching = "Searching" AND
xref_users_searching.competences_id IN (23)


I only need to JOIN xref_users_searching when users.acceptsallworkgroups is 0, but i do need it to be one single query. When doing a normal join i lose all the results where users.acceptsallworkgroups = 1. This is why i am doing a LEFT JOIN


mysql> EXPLAIN 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
-> users.acceptsallworkgroups = '1' OR
-> users.acceptsallworkgroups = '0' AND
-> xref_users_searching.searching = "Searching" AND
-> xref_users_searching.competences_id IN (23);
+----+-------------+------------------------+------------+--------+-------------------------------------+------------------------+---------+--------------------------------------------------+--------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------------+------------+--------+-------------------------------------+------------------------+---------+--------------------------------------------------+--------+----------+--------------------------+
| 1 | SIMPLE | users | NULL | ALL | PRIMARY,users | NULL | NULL | NULL | 313892 | 19.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; Using index |
| 1 | SIMPLE | xref_users_competences | NULL | ref | werkgroep_id,xref_users_competences | xref_users_competences | 8 | gipaa_test.users.id | 1 | 100.00 | NULL |
| 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 | xref_users_searching | NULL | ref | xref_users_searching | xref_users_searching | 8 | gipaa_test.users.id | 1 | 100.00 | Using where; Using index |
+----+-------------+------------------------+------------+--------+-------------------------------------+------------------------+---------+--------------------------------------------------+--------+----------+--------------------------+
5 rows in set, 1 warning (0.01 sec)


mysql> show index from users;
+-------+------------+----------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users | 0 | PRIMARY | 1 | id | A | 283585 | NULL | NULL | | BTREE | | |
| users | 1 | users | 1 | id | A | 313892 | NULL | NULL | | BTREE | | |
| users | 1 | users | 2 | acceptsallworkgroups | A | 313892 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> show index from xref_users_competences;
+------------------------+------------+------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------------+------------+------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| xref_users_competences | 0 | PRIMARY | 1 | id | A | 338228 | NULL | NULL | | BTREE | | |
| xref_users_competences | 1 | werkgroep_id | 1 | competences_id | A | 4 | NULL | NULL | | BTREE | | |
| xref_users_competences | 1 | xref_users_competences | 1 | users_id | A | 323584 | NULL | NULL | | BTREE | | |
| xref_users_competences | 1 | xref_users_competences | 2 | deleted | A | 314578 | NULL | NULL | YES | BTREE | | |
| xref_users_competences | 1 | xref_users_competences | 3 | active | A | 323584 | NULL | NULL | | BTREE | | |
| xref_users_competences | 1 | xref_users_competences | 4 | maxkm | A | 314578 | NULL | NULL | | BTREE | | |
+------------------------+------------+------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.01 sec)

mysql> show index from xref_distance
-> ;
+---------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| xref_distance | 0 | PRIMARY | 1 | postal1 | A | 55757 | NULL | NULL | | BTREE | | |
| xref_distance | 0 | PRIMARY | 2 | postal2 | A | 15729456 | NULL | NULL | | BTREE | | |
| xref_distance | 1 | xref_distance | 1 | postal1 | A | 18753 | NULL | NULL | | BTREE | | |
| xref_distance | 1 | xref_distance | 2 | postal2 | A | 16384476 | NULL | NULL | | BTREE | | |
| xref_distance | 1 | xref_distance | 3 | Distance | A | 16384476 | NULL | NULL | | BTREE | | |
+---------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.01 sec)

mysql> show index from xref_users_searching
-> ;
+----------------------+------------+----------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------------+------------+----------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| xref_users_searching | 0 | PRIMARY | 1 | id | A | 148104 | NULL | NULL | | BTREE | | |
| xref_users_searching | 1 | xref_users_searching | 1 | users_id | A | 159720 | NULL | NULL | | BTREE | | |
| xref_users_searching | 1 | xref_users_searching | 2 | competences_id | A | 159720 | NULL | NULL | | BTREE | | |
| xref_users_searching | 1 | xref_users_searching | 3 | searching | A | 159720 | NULL | NULL | | BTREE | | |
+----------------------+------------+----------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.01 sec)


PS: How can i check the query optimizer settings

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: MYSQL issues with query performance
187
June 30, 2020 06:13AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.