Summing alot of values on HUGE tables

perrohunter

Hi !

Im having some speed problems with my app, and i was thinnking that maybe there could be a faster way to sum the values on a field from my database

First i tryed selecting everything a summing it with php

the i tryed using SUM() within my query

Now, my database its about 1,300,000 rows long, and most of the consults i do are about 2,00 rows

the usual sum of this 2000 rows takes about 2.7 seconds

Is it possible to increase the speed of this process ??

jdiver

Try creating an index on the field you're using SUM() on.

nemonoman

Acutally, the index would be better placed on the columns you are searching to get your 2000 rows.

To SUM() the 2000 rows is practically instantaneous. The time factor is FINDING the rows, in all probablility.

You can prove this by adding some microtime() capture to your scriipt. Check the time for the same query with and without the SUM. DON'T return the rows

$time_start = microtime(true);
$result=mysql_query("SELECT * FROM mytable WHERE 1=1"); //no sum
$time_end = microtime(true);
$time = $time_end - $time_start;
echo "Without sum, $time seconds<BR>";

$time_start = microtime(true);
$result=mysql_query("SELECT SUM(myfield) FROM mytable WHERE 1=1"); $time_end = microtime(true);
$time = $time_end - $time_start;
echo "With sum, $time seconds";

perrohunter

2.97 seconds :mad:

thats alot of time =(

anyway to improve select ?

nemonoman

Hard to say without seeing your query, table structure, and indexes. Also the speed and load of the server affect this.

jdiver

nemonoman wrote:
Acutally, the index would be better placed on the columns you are searching to get your 2000 rows.

Good point. My brain was thinking the column being summed was the same one being searched on, but that of course isn't necessarily the case.

To perrohunter: You can get some really dramatic speed increases simply by indexing correctly. If you post the table structure and the exact query in question, we would have more info to go on.