Results 1 to 2 of 2
Thread: mysql year/month summary
Jul 17, 2003, 16:17 #1
- Join Date
- Feb 2002
- 0 Post(s)
- 0 Thread(s)
mysql year/month summary
I want to summarise transaction data into year/month
date_format(summary_date,'%b-%Y') as m,
sum(qty) as q
WHERE trans_summary.summary_date between '2002-03-01' and '2002-11-31'
GROUP BY LEFT(summary_date,7)
ORDER BY LEFT(summary_date,7);
table : trans_summary
type : range
Extra: where used; Using temporary
With particular regard to query performance Is there a better way to construct this query or data? Is it possible to get a similar result without mysql reporting that it is "Using temporary"?
Many thanks for any insights.
Jul 18, 2003, 01:31 #2
I believe it may be using temporary because your aggregate (SUM) is not performed on an indexed field. Add an index to the [qty] field, see if it helps.
Also I notice you are grouping and ordering on the output of a string function. You could put the YYYYMM data into a new (indexed) field and group and sort and filter on that, this should help too.