MySQL Forums
Forum List  »  Performance

Re: MYSQL issues with query performance
Posted by: Peter Brawley
Date: June 24, 2020 06:55PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: MYSQL issues with query performance
123
June 24, 2020 06:55PM


Sorry, only registered users may post in this forum.

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.