Calculating average (AVG) and grouping by week on large data set takes too long

Posted by caioiglesias on Stack Overflow See other posts from Stack Overflow or by caioiglesias
Published on 2010-04-09T21:08:31Z Indexed on 2010/04/09 21:33 UTC
Read the original article Hit count: 166

Filed under:

mysql

|

average

|

group-by

I'm getting average prices by week on 7 million rows, it's taking around 30 seconds to get the job done.

This is the simple query:

SELECT AVG(price) as price, yearWEEK(FROM_UNIXTIME(timelog)) as week from pricehistory where timelog > $range and product_id = $id GROUP BY week

The only week that actually gets data changed and is worth averaging every time is always the last one, so this calculation for the whole period is a waste of resources. I just wanted to know if mysql has a tool to help out on this.

© Stack Overflow or respective owner

Related posts about mysql

How to remove MySQL completely with config and library files on ubuntu 12.04 gnome 3.0

as seen on Ask Ubuntu - Search for 'Ask Ubuntu'
I tried everything till now: sudo apt-get remove mysql-server mysql-client mysql-common sudo apt-get purge mysql-server mysql-client mysql-common sudo apt-get autoremove and even more commands... But whenever I am trying to locate mysql. I get a no. of files related to mysql command: shell>>… >>> More
mysql: Cannot load from mysql.proc. The table is probably corrupted

as seen on Server Fault - Search for 'Server Fault'
Mysql was started: /usr/bin/mysqld_safe --datadir=/srv/mysql/myDB --log-error=/srv/mysql/logs/mysqld-myDB.log --pid-file=/srv/mysql/pids/mysqld-myDB.pid --user=mysql --socket=/srv/mysql/sockets/mysql-myDB.sock --port=3700 but when I'm trying to do something: ERROR 1548 (HY000) at line 1: Cannot… >>> More
Why is there a /etc/init.d/mysql file on this Slackware machine? How could it have gotten there?

as seen on Server Fault - Search for 'Server Fault'
A client of my IT-consulting service owns a web-development shop. He's been having problems with a Slackware 12.0 server running MySQL 5.0.67. The machine was set up by the client's sysadmin, who left on bad terms. My client no longer employs a sysadmin. As far as I can tell, the only copy of MySQL… >>> More
mysql: Bind on unix socket: Permission denied

as seen on Server Fault - Search for 'Server Fault'
Can't start mysql with: sudo /usr/bin/mysqld_safe --datadir=/srv/mysql/myDB --log-error=/srv/mysql/logs/mysqld-myDB.log --pid-file=/srv/mysql/pids/mysqld-myDB.pid --user=mysql --socket=/srv/mysql/sockets/mysql-myDB.sock --port=3700 120222 13:40:48 mysqld_safe Starting mysqld daemon with databases… >>> More
MySQL – Learning MySQL Online in 6 Hours – MySQL Fundamentals in 320 Minutes

as seen on SQL Authority - Search for 'SQL Authority'
MySQL is one of the most popular database language and I have been recently working with it a lot. Data have no barrier and every database have their own place. I have been working with MySQL for quite a while and just like SQL Server, I often find lots of people asking me if I have a tutorial which… >>> More

Related posts about average

Efficient way of calculating average difference of array elements from array average value

as seen on Stack Overflow - Search for 'Stack Overflow'
Is there a way to calculate the average distance of array elements from array average value, by only "visiting" each array element once? (I search for an algorithm) Example: Array : [ 1 , 5 , 4 , 9 , 6 ] Average : ( 1 + 5 + 4 + 9 + 6 ) / 5 = 5 Distance Array : [|1-5|, |5-5|, |4-5|, |9-5|, |6-5|]… >>> More
Average of Average in one row

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi there, I'm not sure if this is possible what I'm trying to achieve. I want to get the avg of averaged columns. SELECT avg(col1), avg(col2), avg(col3) FROM tbl My Result should be the avg of all three avg columns, is this possible? Something like this SELECT avg( col1, col2, col3) FROM tbl doesn't… >>> More
Real average filesize in a set of files...

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi, I have an apparently "simple" problem but I can't find the solution for some reason... I have n millions files of different sizes and I want to find the average filesize. To simplify it, I grouped them in multiples of 16KB. < 16 KB = 18689546 files < 32 KB = 1365713 files < … >>> More
How can I determine which processes are using up the most CPU?

as seen on Server Fault - Search for 'Server Fault'
05:54:17 up 6 days, 9:54, 1 user, load average: 15.70, 8.04, 4.56 Load average is a LOT higher than it should be. It was HALF of that a moment ago, I used ps aux to see which processes were using a lot of cpu, and the httpd was using a TON. It had several processes running with 0.9 each. So I… >>> More
raid 1 and high load average

as seen on Server Fault - Search for 'Server Fault'
i have a server with high load average, I think the problem is the raid 1. cat /proc/mdstat Personalities : [raid1] md0 : active raid1 sdb1[1] sda1[0] 256896 blocks [2/2] [UU] md3 : active raid1 sdb3[1] sda3[0] 2562240 blocks [2/2] [UU] md4 : active raid1 sdb5[1] sda5[0] … >>> More