CODEWITHSUNDEEP
mysqlmax
Vunus
Vunus

Reputation: 257

Optimizing MySQL Query With MAX()

Apologies if this has been asked before but is there any way, at all, I can optimize this query to run faster. At the minute it takes about 2 seconds which while isn't a huge amount it is the slowest query on my site, all other queries take less that 0.5 secs.

Here is my query:

SELECT SQL_CALC_FOUND_ROWS MAX(images.id) AS maxID, celebrity.* FROM images
JOIN celebrity ON images.celeb_id = celebrity.id
GROUP BY images.celeb_id
ORDER BY maxID DESC
LIMIT 0,20

Here is an explain:

1 SIMPLE celebrity ALL PRIMARY NULL NULL NULL 536 Using temporary; Using filesort
1 SIMPLE images ref celeb_id celeb_id 4 celeborama_ignite.celebrity.id 191

I'm at a loss at how to improve the performance in this query further. I'm not super familiar with MySQL, but I do know that it is slow because I am sorting on the data created by MAX() and that has no index. I can't not sort on that as it gives me the results needed, but is there something else I can do to prevent it from slowing down the query?

Thanks.

Upvotes: 3

Views: 1349

Answers (5)

newtover
newtover

Reputation: 32084

SELECT STRAIGHT_JOIN *
FROM (
  SELECT MAX(id) as maxID, celeb_id as id
  FROM images
  GROUP BY celeb_id
  ORDER by maxID DESC
  LIMIT 0, 20) as ids
JOIN celebrity USING (id);

the query does not allow row number precalculation, but an additional:

SELECT COUNT(DISTINCT celeb_id)
FROM images;

or even (if each celebrity has an image):

SELECT COUNT(*) FROM celebrity;

will not cost much, because can easily be cached by the query cache (if it not switched off).

Upvotes: 0

Uday Sawant
Uday Sawant

Reputation: 5798

MYSQL doesn't perform so good with joins. i would recommend to dividing your query in two. that is in first query select the Celeb and then select image. Simply avoid joins. Check out this link - http://phpadvent.org/2011/a-stitch-in-time-saves-nine-by-paul-jones

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562230

I would get the latest image this way:

SElECT c.*, i.id AS image_id
FROM celebrity c
JOIN images i ON i.celeb_id = c.id
LEFT OUTER JOIN images i2 ON i2.celeb_id = c.id AND i2.id > i.id
WHERE i2.id IS NULL
ORDER BY image_id DESC
LIMIT 0,20;

In other words, try to find a row i2 for the same celebrity with a higher id than i.id. If the outer join fails to find that match, then i.id must be the max image id for the given celebrity.

SQL_CALC_FOUND_ROWS can cause queries to run extremely slowly. I've found some cases where just removing the SQL_CALC_FOUND_ROWS made the query run 200x faster (but it could also make only a small difference in other cases, it depends on the table, so you should test both ways).

If you need the equivalent of SQL_CALC_FOUND_ROWS, just run a separate query:

SELECT COUNT(*) FROM celebrity;

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115510

I think you need a compound index on (celeb_id, id) in table images (supposing it's a MyISAM table), so the GROUP BY celeb_id and MAX(id) can use this index.

But with big tables, you'll probably have to follow @zerkms' advice and add a new column in table celebrity

Upvotes: 0

zerkms
zerkms

Reputation: 254886

If you really need fast solution - then don't perform such queries in runtime.

Just create additional field last_image_id in celebrity table and update it on event of uploading of new image (by trigger or your application logic, doesn't matter)

Upvotes: 2

Related Questions