Very slow MYSQL query, more than 25 seconds, how to improve?

Asked

Viewed 792 times

-3

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

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

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

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 consultation LEFT JOIN (SELECT * FROM osrs_photos ORDER BY ordering ASC) f ON f.pro_id = p.id serves for me to catch the photo with the smaller Ordering, she that is making the consultation slow

mysql select

  • Try to change the SELECT * FROM osrs_photos for a query that returns only the columns you use in your query. See if you have any gains.

  • 1

    1000 records is practically insignificant. Your problem is surely the image files in the bd. The ideal is not to have them in the comics, but outside, controlling only the address by the bank. Probably 1 image, worth 1000 records of your other tables! rs

  • @Rbz It would not be an option for it to store in the bank but in a compressed form?

  • @Rbz is then, is that the query works like this, it will bring all immovable and 1 photo, and this photo has to be the smallest (the Ordering column has to be the smallest) I made a test using Ordering =1, hence it goes super fast, the problem is that it has records that do not have Ordering 1, the smallest is 2 for example

  • @The problem is not space, but processing. Compressing will probably get worse, as the database will have 1 more step to open the image.

  • @Victorlaio how to compress asism? (work with mysql)

  • @The decompression process would be done by the backend so as not to overload the bank, and I have seen several processes that can decrease the size of an image drastically. In the companies I worked I never recommended storage on the server due to security.

  • @Leandro, it would compress the image or use processes in the backend to decrease the size of the image before saving it in the database to avoid consuming so much resource. Like what Whatsapp does with images.

  • @Victorlaio as I understand it, you said to dimuir the image size, this? if it is, it is not necessary, what is taking actually, is to find the reference of that image in the bank

  • @Victorlaio why you do not do the search without the image and after that return goes in the bank again (async) to upload the image? I think it is a very simple solution and should solve the problem

  • @Leandromarzullo You tried to use a SELECT TOP, and search for the first record found, making a ORDER BY by another field, since you have field NULL in ORDERING? This Join is probably causing the slowness LEFT JOIN (SELECT * FROM osrs_photos ORDER BY ordering ASC) f ON f.pro_id = p.id

  • @Rbz taking advantage of his comment that 1000 records is insignificant, from more or less records qts, I consider as large (considering a table of 5 columns)

  • @Leandromarzullo It depends a lot on your server, your database, indexes, etc. But "basically" a table with only 5 columns (varchar, int, thinking of the most used), can put millions and millions! rs

  • show, thanks for the teachings...rs

Show 9 more comments

1 answer

4


Have you tried it like 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 queries perform a tablescan on osrs_photos and osrs_property_category tables. Try the way I showed you to see if it improves your performance.

I hope I helped. D

  • Show, it worked, yes, it took less than half a second, just so I understand, what exactly did you do? Thank you!

  • @Leandromarzullo he eliminated all the queries he was using in the joins. As the table already has reference, you can use direct. These queries would be necessary either for lack of reference, or something specific. But still the ideal is not to store images in bd. Look here at Sopt who has enough on the subject.

  • @Rbz, thanks, it’s like my consultation is 3 consultations in one and this is now one, this? Valew!

  • The difference is that you put only the table, it will only make the direct reference. If you put a query, it executes it first, then references.

Browser other questions tagged mysql select

You are not signed in. Login or sign up in order to post.