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
Subject
Written By
Posted
how to optimize this query
March 12, 2009 10:15PM
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.