MySQL Forums
Forum List  »  Performance

MYSQL issues with query performance
Posted by: Wouter Platteeuw
Date: June 24, 2020 02:47PM

Hey All,

I am new to mysql and have some issues with a query im building.
For simplicity sake i am just selecting one row.

In this scenario i have 2 querys which look alike but one completes in around 0.04 seconds while the other query takes more then 1 second (around 1.4)

Query1: (0.04 secs)

SELECT

competences.name AS compname

FROM
users
JOIN
xref_users_competences
ON xref_users_competences.users_id = users.id
LEFT 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 = '0'
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
EXPLAIN


Query2 (1.4 sec)

SELECT

competences.name AS competence_name

FROM
users
JOIN
xref_users_competences
ON xref_users_competences.users_id = users.id
LEFT JOIN
xref_distance
ON xref_distance.postal2 = users.postal
AND xref_distance.postal1 = '6663'

JOIN
competences
ON xref_users_competences.competences_id = competences.id
WHERE
users.acceptsallworkgroups = '1'
AND xref_users_competences.deleted = 0
AND xref_users_competences.active = '1'
AND xref_users_competences.deleted = '0'
AND xref_distance.distance < 92
AND xref_distance.distance > xref_users_competences.maxkm
AND xref_users_competences.users_id != 1592824499514626

Even if i force it to use the same indexes as query1 it takes over a second


As end result i need to combine these querys into one but using the current indexes this would take forever. This is the combined query i use:
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,
users.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
users
JOIN
xref_users_competences
ON xref_users_competences.users_id = 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 = '0'
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

OR

users.acceptsallworkgroups = '1'
AND xref_users_competences.deleted = 0
AND xref_users_competences.active = '1'
AND xref_users_competences.deleted = '0'

AND xref_distance.distance < 92
AND xref_distance.distance > xref_users_competences.maxkm
AND xref_users_competences.users_id != 1592824499514626

We would also like to order by a specific row if possible (average_rating)
What am i doing wrong?
Any help would be greatly appreciated.

Options: ReplyQuote


Subject
Views
Written By
Posted
MYSQL issues with query performance
488
June 24, 2020 02:47PM


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.