Re: MYSQL issues with query performance

[Wouter Platteeuw]

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