[SOLVED] Time consuming query

[Anfaenger]

Hi all,

 

I have a table with id's, dates and numeric values. I am trying to calculate an average for different calendar years using only the latest date entry for each unique id. Here is the query that works and returns the correct result:

select avg(value)from table p
where p.date=(select max(b.date) from table b where year(date)='2008/2007/2006/etc' and p.id=b.id)

 

The problem is, that this query takes very long before returning a result (nearly 2 minutes). There are close to 400,000 entries in that table and round about 5,000 of them will be used for calculating the average for each year.

 

My question is, does anyone have a more elegant and faster solution to achieve the same results or can I optimize the query somehow for faster results? Would it be advisable to put some information  in a new table for better performance?

 

Thank you very much

[Phoenix~Fire]

you might just want to save the query result and update it periodically...

or save the max(id) from table b

 

the issue here is you are getting rows in table b to the power of table p = number of rows queried overall. 

 

 

[corbin]

You would probably be better off storing YEAR(date) already calculated (breaks some ideas of normalization but helps with performance ;p).  Also, hopefully everything is indexed well.

 

 

 

Some other changes might be possible, but like Phoenix~Fire said, it's just a beast of a query.

[Anfaenger]

Thank you for your reply.

 

I am already storing calculated years so that only the last year needs to be calculated over and over. However, sometimes data for previous years will be added and the whole query needs to be proccessed. I think I will have to come up with another way to store the data so that it is faster.

 

Thanks for your input.