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)

Query is 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

  1. 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/100 seconds . Based on this size of data is 150 * 10^6 * 4/10^6= 600 MB assuming each year is of 4 byte long. So total time to read the data from disk should be 600/100 = 6 secs

  2. 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 instructions just 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 ?

  • 1
    You're not just doing a count(*), you're doing a group by, which is more expensive. Anyway, for starters, edit your question with an explain plan of the query – Philᵀᴹ May 12 '19 at 10:25
  • @Philᵀᴹ I agree there are two operation i.e group by and then count but still do you think it will be close to 96 billion instructions. If i think it will be doing group by internally it will maintaining some kind of map(like HashMap in java) with key as year and value as count . Also the explain plan is given under the link I mentioned . I did not paste it here to avoid verbosity – user3198603 May 12 '19 at 10:59
  • 1
    Without proper instrumentation and actually measuring where the bottlenecks are, this question is impossible to answer I’m afraid. – Philᵀᴹ May 12 '19 at 11:50

Rule of Thumb: If the necessary data is cached, the query will run 10x faster than if not. I have seen this in a wide variety of queries. (Of course, the "10" varies a lot, too.)


 SELECT yeard, COUNT(*) FROM t GROUP BY yeard;


The Optimizer will scan the entire INDEX(yeard) of 150M 'rows'. It does not have to do much more (at a high level) than count rows until yeard changes. That is the INDEX and the GROUP BY work well together; they are not two separate steps.

  • About 400 index entries per 16KB block.
  • 100 blocks/second is a more realistic estimate for InnoDB hitting HDD. (This goes away for any blocks that are cached.)
  • 150M/400/100 = 3750 seconds. So, 54s for SSD seems about optimistic. Or some of the blocks were cached.

If you run the query a second time (after all the blocks are cached), it may run 10x faster.

It's hard to say how many CPU cycles a query will take. The code to "get the next row" (even of an index) is somewhat generic, and meanwhile, it is building (probably) an in-memory hash of the results. There are also steps to parse the query, decide how to optimize it, deliver the results, etc.

The EXPLAIN in this example will say "Using index", meaning that it only used the index's BTree and did not need to touch the data's BTree.

The BTree is really a B+Tree, meaning that consecutive blocks are linked, making linear scans (as with your query) efficient.

| improve this answer | |
  • As you said The Optimizer will scan the entire INDEX(yeard) of 150M 'rows'. It does not have to do much more (at a high level) than count rows until yeard changes so you are saying majority of the 54 secs will utilized in disk scan not in computing like count and group by . Is that right ? – user3198603 May 13 '19 at 1:50
  • Other couple of observations Point-1 you said "About 400 index entries per 16KB block" I believe assumption is 40B for each year ? Point-2 you said "100 blocks/second is a more realistic estimate for InnoDB hitting HDD" This means it will 16*100KB per second i.e 1.6 MB per sec. Per my reading , on an average OS can read 100MB per sec from HDD . Is not that correct ? Point-3 you said "150M/400/100 = 3750 seconds. So, 54s for SSD seems about optimistic. " This means read speed of SSD is 70 times faster than HDD ? – user3198603 May 13 '19 at 1:59
  • @user3198603 - Most of the numbers I mentioned are subject to critique. Some are from experience -- every few months I see some data which feeds into, say, the 100 hits/sec. Note how many things are hidden behind that -- caching, conflicting activity, arm motion, 16KB is 32 disk sectors, blocks might not be consecutively placed, etc, etc. The 400 comes from estimating the size of that index, adding overhead for each 'row', adding overhead for the way a BTree works, etc. I did not even start with good raw data -- datatype of yeard, what is PK? – Rick James May 13 '19 at 3:16
  • I totally understand these numbers are high level numbers and subject to critique. Can you please share your thoughts on my first comment and Point-2 and 3 as well ? – user3198603 May 13 '19 at 5:15
  • @user3198603 - 2 The OS may be able to read 100MBs, but can the HDD (without RAID) deliver that much. Also, the 100MB is probably sequential, not random. (OK, it is a stretch to get a factor of 60 discrepancy.) 3 I don't have a good feel for the speed difference between HDD and SDD. From InnoDB's perspective, there are other delays (OS, parsing, etc) that will keep InnoDB from seeing 70x even if the raw hardware is 70x. – Rick James May 13 '19 at 18:06

Not the answer you're looking for? Browse other questions tagged or ask your own question.