SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2002
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql year/month summary

    I want to summarise transaction data into year/month

    EXPLAIN SELECT
    date_format(summary_date,'%b-%Y') as m,
    sum(qty) as q
    FROM trans_summary
    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);

    this returns:
    table : trans_summary
    type : range
    possible_keys: summary_date
    key: summary_date
    key_len:3
    ref: NULL
    rows: 23503
    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.

  2. #2
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •