MySQL Forums :: Newbie :: how to optimize this query


Advanced Search

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 Adrian Nye 03/12/2009 10:15PM
Re: how to optimize this query Rick James 03/13/2009 07:47PM
Re: how to optimize this query Adrian Nye 03/14/2009 08:11PM
Re: how to optimize this query Rick James 03/14/2009 08:50PM
Re: how to optimize this query Adrian Nye 03/16/2009 08:14AM
Re: how to optimize this query Adrian Nye 03/16/2009 08:33AM
Re: how to optimize this query Adrian Nye 03/16/2009 05:05PM
Re: how to optimize this query Rick James 03/16/2009 09:15PM
Re: how to optimize this query Minski Browar 03/16/2009 10:29PM
Re: how to optimize this query Chris Preston 03/17/2009 04:32AM
Re: how to optimize this query Adrian Nye 03/17/2009 07:58AM
Re: how to optimize this query Chris Preston 03/17/2009 02:07PM
Re: how to optimize this query Ga Td 06/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.