Query MYSQL too slow, more than 25 seconds, how to improve?

Question

Query MYSQL too slow, more than 25 seconds, how to improve?

Navigation

#1 by (4 votes)

-3

I have a query in mysql that is very slow, more than 25 seconds.

The base is quite large, something around 1,000 records of real estate (table osrs_properties) and 16,000 records of photos (table osrs_photos).

The query without the photo takes half a second, but with a photo it takes 25, how can I improve the query or table to increase the query speed?

Below the query:

SELECT p.ref,p.pro_name, p.id, p.pro_alias, p.precovenda, f.image as fotoimg, MIN(f.ordering) AS foto, p.city, p.bed_room, p.rooms, p.parking, p.square_feet
FROM osrs_properties p
LEFT JOIN (SELECT * FROM osrs_photos  ORDER BY ordering ASC) f ON f.pro_id = p.id
LEFT JOIN (SELECT category_id, pid FROM osrs_property_categories) c ON c.pid = p.id
WHERE p.id > 0
GROUP BY p.pro_name, p.id
ORDER BY p.pro_name, foto DESC
LIMIT 21

The query LEFT JOIN (SELECT * FROM osrs_photos ORDER BY ordering ASC) f ON f.pro_id = p.id is for me to take the photo with the lowest ordering, it is leaving the query slow

mysql select

asked by anonymous 10.12.2018 / 13:18

1 answer

Name Display on Diagonal (Top to Bottom) Tools for working with graphics [closed]

score 4 · Accepted Answer

Have tried this:

SELECT p.ref, p.pro_name, p.id, p.pro_alias, p.precovenda, f.image as fotoimg, 
MIN(f.ordering) AS foto, p.city, p.bed_room, p.rooms, p.parking, p.square_feet
FROM osrs_properties p
LEFT JOIN osrs_photos f ON f.pro_id = p.id
LEFT JOIN osrs_property_categories c ON c.pid = p.id
WHERE p.id > 0
GROUP BY p.pro_name, p.id
ORDER BY p.pro_name, foto DESC
LIMIT 21

All your subqueries make a tablescan in the osrs_photos and osrs_property_category tables. Try the way I showed it to see if it improves performance.

I hope I have helped. : D