From SQl case study
System configuration is
Software: Percona 5.6.15-63.0. Hardware: Supermicro; X8DTG-D; 48G of RAM; 24xIntel(R) Xeon(R) CPU L5639 @ 2.13GHz, 1xSSD drive (250G)
select yeard, count(*) from ontime group by yeard where yeard is indexed column
The query is simple, however, it will have to scan 150M rows. Here is the results of the query (cached):
The query took 54 seconds and utilized only 1 CPU core
My understanding :- Per my calculation it should have finished in much less time based on above system shared configuration and data to scan. I know I am wrong and missing something but what is that ?
Here is my calculation for seconds
For HDD, average time taken to read 100MB of data from disk is around 1 sec. For SSD is 5 to 10X faster. But still conservatively if I assume the speed of 100MB per seconds , time to read the data will be
size of data in MB/100seconds . Based on this size of data is
150 * 10^6 * 4/10^6= 600 MBassuming each year is of 4 byte long. So total time to read the data from disk should be
600/100 = 6 secs
Now it 2.13GHz CPU which means that 2 billion cycles per seconds which on average means 2 billion instruction per second per core. Now actual time taken to execute the query was 54 seconds which means time taken to compute the instruction was approx
54-6 = 48 seconds. Does it mean to it had to execute around
48 * 2 = 96 billion instructionsjust to calculate the count and group by year or am I missing something ?
May be more time in disk read or count looks simple but internally it involves number of instructions ?