Professional Web Applications Themes

speeding up a query that's being grouped by an aggregate function - MySQL

I'm working with a table whose date column is stored as an int(11) and am trying to group it by the year and month. Here's what I have, so far: SELECT from_unixtime(purchase_date,'%Y') AS purchase_year, from_unixtime(purchase_date,'%m') AS purchase_month, COUNT(*) AS purchase_total FROM purchases GROUP BY purchase_year, purchase_month; Unfortunately, this is kinda slow - any ideas as to how I might speed it up? Thanks!...

  1. #1

    Default speeding up a query that's being grouped by an aggregate function

    I'm working with a table whose date column is stored as an int(11) and
    am trying to group it by the year and month. Here's what I have, so
    far:

    SELECT from_unixtime(purchase_date,'%Y') AS purchase_year,
    from_unixtime(purchase_date,'%m') AS purchase_month, COUNT(*) AS
    purchase_total
    FROM purchases
    GROUP BY purchase_year, purchase_month;

    Unfortunately, this is kinda slow - any ideas as to how I might speed
    it up? Thanks!

    yawnmoth Guest

  2. #2

    Default Re: speeding up a query that's being grouped by an aggregate function

    yawnmoth wrote: 

    Try EXPLAINing your statement. I suspect it requires a table scan. If
    so, a DATE field would be better.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: speeding up a query that's being grouped by an aggregate function

    On Apr 5, 9:35 pm, Jerry Stuckle <net> wrote: 


    >
    > Try EXPLAINing your statement. I suspect it requires a table scan. If
    > so, a DATE field would be better.[/ref]
    You're right - a DATE field would be better. Unfortunately, the
    database already exists and converting it over is not an option for
    me.

    As for an EXPLAINation... well... I create two "variables" -
    purchase_year and purchase_month, with aggregate functions, and group
    by them. I don't know what more of an explanation I can give...

    yawnmoth Guest

  4. #4

    Default Re: speeding up a query that's being grouped by an aggregate function

    On Apr 5, 9:35 pm, Jerry Stuckle <net> wrote: 


    >
    > Try EXPLAINing your statement. I suspect it requires a table scan. If
    > so, a DATE field would be better.[/ref]
    I don't have ALTER TABLE permissions, but... I am curious, none-the-
    less, how I'd go about converting a unix timestamp, stored as an
    INT(11) to a DATE, while preserving the orig date?

    yawnmoth Guest

  5. #5

    Default Re: speeding up a query that's being grouped by an aggregate function

    >> > SELECT from_unixtime(purchase_date,'%Y') AS purchase_year, 
    >> 
    >>
    >> Try EXPLAINing your statement. I suspect it requires a table scan. If[/ref][/ref]

    By this, he means run the query EXPLAIN SELECT ...
    which will tell you how MySQL intends to process the query. He's not
    asking YOU to do the explaining.
     
    >I don't have ALTER TABLE permissions, but... I am curious, none-the-
    >less, how I'd go about converting a unix timestamp, stored as an
    >INT(11) to a DATE, while preserving the orig date?[/ref]

    Add a date column:

    ALTER TABLE purchases add purchase_date_date;
    ("not null" optional if purchase_date is "not null").

    Now put data in it (all rows):

    UPDATE purchases set purchase_date_date = from_unixtime(purchase_date);

    If you have CREATE TEMPORARY TABLE privileges, it might be worthwhile
    creating a new table with the date-type purchase_date in it, then
    use it in joins, then drop it when you're done.

    Gordon Guest

  6. #6

    Default Re: speeding up a query that's being grouped by an aggregate function

    On Apr 5, 10:40 pm, org (Gordon Burditt) wrote: [/ref]
    > [/ref]
    > [/ref]
    >
    > By this, he means run the query EXPLAIN SELECT ...
    > which will tell you how MySQL intends to process the query. He's not
    > asking YOU to do the explaining.[/ref]
    Oh. Eep. Here's what EXPLAIN says:

    id = 1
    select_type = SIMPLE
    table = purchases
    type = index
    possible_keys = NULL
    key = purchase_date
    key_len = 4
    ref = NULL
    rows = 100968
    Extra = Using index; Using temporary; Using filesort

    According to <http://dev.mysql.com/doc/refman/5.0/en/explain.html>,
    the last two are ones I should look out for.

    yawnmoth Guest

  7. #7

    Default Re: speeding up a query that's being grouped by an aggregate function

    "yawnmoth" <com> wrote in
    news:googlegroups.com:
     
    >> 
    >> 
    >>
    >> By this, he means run the query EXPLAIN SELECT ...
    >> which will tell you how MySQL intends to process the query. He's not
    >> asking YOU to do the explaining.[/ref]
    > Oh. Eep. Here's what EXPLAIN says:
    >
    > id = 1
    > select_type = SIMPLE
    > table = purchases
    > type = index
    > possible_keys = NULL
    > key = purchase_date
    > key_len = 4
    > ref = NULL
    > rows = 100968
    > Extra = Using index; Using temporary; Using filesort
    >
    > According to <http://dev.mysql.com/doc/refman/5.0/en/explain.html>,
    > the last two are ones I should look out for.[/ref]

    Explain is telling you that it is not using any index for your query.
    Index your "purchase_date", "purchase_month" and "purchase_year" columns
    and give it another go!


    Good Guest

  8. #8

    Default Re: speeding up a query that's being grouped by an aggregate function

    yawnmoth wrote: 
    >> By this, he means run the query EXPLAIN SELECT ...
    >> which will tell you how MySQL intends to process the query. He's not
    >> asking YOU to do the explaining.[/ref]
    > Oh. Eep. Here's what EXPLAIN says:
    >
    > id = 1
    > select_type = SIMPLE
    > table = purchases
    > type = index
    > possible_keys = NULL
    > key = purchase_date
    > key_len = 4
    > ref = NULL
    > rows = 100968
    > Extra = Using index; Using temporary; Using filesort
    >
    > According to <http://dev.mysql.com/doc/refman/5.0/en/explain.html>,
    > the last two are ones I should look out for.
    >[/ref]

    And therein lies your problem. MySQL has to scan the entire table and
    call unixtime() twice for every row in the table. That's a lot of
    overhead, and it will get worse as your table grows.

    Additionally, MySQL can't use an index for sorting (for the GROUP BY
    clause) because you don't have a real column to sort on.

    I think the only way you'll be able to speed this up will be to alter
    the table to include a DATE column.



    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

Similar Threads

  1. Help with grouped query
    By Captain Special in forum Coldfusion Database Access
    Replies: 11
    Last Post: December 7th, 01:20 PM
  2. Aggregate Function Nightmare
    By Henweigh99 in forum Macromedia ColdFusion
    Replies: 15
    Last Post: July 14th, 02:54 AM
  3. Aggregate Bitwise OR Function
    By Mark in forum IBM DB2
    Replies: 3
    Last Post: September 25th, 06:53 PM
  4. aggregate query help
    By Vishal in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: August 5th, 03:46 PM
  5. Replies: 3
    Last Post: July 4th, 01:53 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139