CODEWITHSUNDEEP
mysqlsql
Shane Jones
Shane Jones

Reputation: 905

Optimising a MySQL query

So I have the below SQL

 SELECT i.id, r.full_name, i.file_name, i.lat, i.lon,   c.name as
 category_name,     
    NOW() - i.timestamp AS age,         
    (SELECT count(*) FROM votes WHERE image_id = i.id) as total_votes 
 FROM images i INNER JOIN registrations r 
    ON i.user_id = r.id 
 INNER JOIN categories c ON i.category_id = c.id 
 WHERE i.moderated=1 
 ORDER BY total_votes DESC
 LIMIT 25

This averages at 10-15 seconds to run on my local environment.

Removing the ORDER BY part drops it down to 0.02ish.

Is there any way to speed up the ordering in the main statement?

UPDATE 1: Explain for query below. Explain for query

UPDATE 2 : Rewrote the query and added some indexes in

Query

SELECT  votes.image_id, count(votes.id) as total_votes,
        images.file_name, images.lat, images.lon, NOW() - images.timestamp AS age,
        registrations.full_name, 
        categories.name
FROM votes
LEFT JOIN images
    ON votes.image_id = images.id
LEFT JOIN registrations
    ON images.user_id= registrations.id
LEFT JOIN categories
    ON images.category_id = categories.id
GROUP BY votes.image_id 
ORDER BY total_votes DESC
LIMIT 25

Current Explain for the query:

enter image description here

Upvotes: 0

Views: 65

Answers (3)

Amit Deshmukh
Amit Deshmukh

Reputation: 23

Using TEMP TABLES for votes and registrations tables will help you to speed up the query.

Upvotes: 0

Kristiyan
Kristiyan

Reputation: 1663

Create in table images one column "votes". If have vote up for image - increment, if have vote down - decrement. You can have table votes parallely and check if have success inert then increment.

With this you will remove this your subselect.

After this create INDEX in images with ORDER BY votes DESC and 'moderated = 1`.

BTW, are you sure that your indexs are okey? Also think about INNER JOIN with categories.

Upvotes: 2

fancyPants
fancyPants

Reputation: 51868

Your subquery is suboptimal :)

 SELECT i.id, r.full_name, i.file_name, i.lat, i.lon,   c.name as
 category_name,     
    NOW() - i.timestamp AS age,         
    COUNT(*) as total_votes 
 FROM images i INNER JOIN registrations r 
    ON i.user_id = r.id 
 LEFT JOIN votes ON i.id = votes.image_id

 INNER JOIN categories c ON i.category_id = c.id 
 WHERE i.moderated=1 
 GROUP BY i.id
 ORDER BY total_votes DESC
 LIMIT 25

If this doesn't help, you'll have to provide table structures and and explain plan of the query.

Upvotes: 1

Related Questions