MySQL Forums
Forum List  »  Newbie

how to optimize this query
Posted by: Adrian Nye
Date: March 12, 2009 10:15PM

I have a table with pk, species, date in each row (plus other columns).
I want to select the one row for each species that has the most recent date. Currently I have the following which works slowly:

SELECT f.id, f.species_name, f.date from (select species_name, max(date) as maxdate from lister_importedsighting WHERE user_id=%s GROUP BY species_name)
as x inner join lister_importedsighting as f on f.species_name = x.species_name and f.date = x.maxdate

With 30k rows, this takes 47 seconds which is way too long (it's a web application).
By the way MySQL 5.1.

One more thing, I need to be able to add up to three other columns to the inner where clause (applied before the maxdate is calculated).

What can I do to optimize this?

Thanks

Options: ReplyQuote


Subject
Written By
Posted
how to optimize this query
March 12, 2009 10:15PM
June 12, 2009 06:25AM


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.