How much does it cost to update an index?

I was asked today about what is the cost of adding an index on a frequently updated column ( like a timestamp, count, or weight )… typically my answer is it depends. But for this question it was narrowed down to a specific case. An update on a secondary index based on a PK lookup. I decided to try and give an exact answer. I hacked the Juice DB Benchmark to attack my medium sized table ( which magically already had a count column in it ). I then cranked up the test. A few more details Query 23 updated a column without an index, queries 21,23,24 updated the d_count column. query 21 adds 5 to the count, query 22 adds 150, query 24 subtracts 1…. here are the results:

With a solo index on d_count:

Run Number:  86  threads:  8 Length :  340 LoadType: upd 
Total Test Runtime = 375.245010137558 seconds, limiting results to 300 seconds however
QNum:     21 ... QCount:  78448 ... QTime:   0.003985 ... Max:   0.095937 ... FlatTime:   0.003673  ... Min5%:   0.000165  ... Max5%:   0.013396 
QNum:     22 ... QCount:  78444 ... QTime:   0.003987 ... Max:   0.094398 ... FlatTime:   0.003678  ... Min5%:   0.000161  ... Max5%:   0.013350 
QNum:     23 ... QCount:  78444 ... QTime:   0.003839 ... Max:   0.075958 ... FlatTime:   0.003547  ... Min5%:   0.000130  ... Max5%:   0.012776 
QNum:     24 ... QCount:  78442 ... QTime:   0.003950 ... Max:   0.091845 ... FlatTime:   0.003642  ... Min5%:   0.000158  ... Max5%:   0.013274 

Let me briefly explain the output, QTime is the average time for that query type, max is the longest run time for that query, flat time is the average query time minus the top and bottom 5% runtimes. You can pick which one you prefer, but I prefer the flat time. In this case the difference in runtime was around a 100-130 microseconds.

With a solo index on d_count and an index on loc_id, d_count:

Run Number:  87  threads:  8 Length :  340 LoadType: upd 
Total Test Runtime = 375.251009941101 seconds, limiting results to 300 seconds however
QNum:     21 ... QCount:  79111 ... QTime:   0.003987 ... Max:   0.061960 ... FlatTime:   0.003663  ... Min5%:   0.000178  ... Max5%:   0.013611 
QNum:     22 ... QCount:  79108 ... QTime:   0.004010 ... Max:   0.068994 ... FlatTime:   0.003686  ... Min5%:   0.000173  ... Max5%:   0.013675 
QNum:     23 ... QCount:  79107 ... QTime:   0.003785 ... Max:   0.081044 ... FlatTime:   0.003466  ... Min5%:   0.000116  ... Max5%:   0.013176 
QNum:     24 ... QCount:  79105 ... QTime:   0.003955 ... Max:   0.103459 ... FlatTime:   0.003626  ... Min5%:   0.000171  ... Max5%:   0.013620 

Now the time goes up to 160-200 microseconds.

Don’t take this as an authoritative answer because their are a lot of other factors that may impact this number, but if you backed me in a corner I would be comfortable giving 100-130 microseconds answer. Yes I did notice that my query times involving 2 indexes were on the whole slightly lower then the single index… not sure why… but I reran this and got similar results.

This entry was posted in benchmark, innodb internals, Matt, mysql, performance. Bookmark the permalink.

Comments are closed.