MySQL can usually use just one index per table in a query, so your queries are easier to optimise when rewritten as follows ...
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';
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';
The left sides of the Left Joins won't optimise well because Left Joins require reading all left-sided rows. Also <> is infamous for not optimising. So if such logic is required, you may be best off preparing or maintaining denormalised intermediate tables that build in such results. Apart from that, the queries might run best with these indexes ...
users(id,acceptsallworkgroups),
competences(id,deleted,active)
xref_distance(postal1,postal2,distance)
xref_users_searching(users_id,searching,competences_id)
... orders in each case depending on the relative selectivity of those columns.
If we need to go further, we'll need the MySQL version number, query optimizer settings, innodb_buffer_pool_size, amount of RAM available, and Explain Format=tree results on each query, perhaps also on the combined query.
Edited 1 time(s). Last edit at 06/25/2020 02:31AM by Peter Brawley.