1

I have a MySQL table with 3 columns on which I'd like to use a multi-column index. Column A is TINYINT, B is SMALLINT and C is VARBINARY (16). Should I use the index as A, B, C, because A has lower granularity than B and B lower than C to achieve maximum INSERT speed?

pedmillon
  • 143
  • 3
  • 13

2 Answers2

2

(Note: This answer clarifies or disagrees with some of the comments already written.)

DELETEs are slowed down because of deleting the index entries. UPDATEs may be slowed down -- it depends on whether an indexed column is changed.

SELECTs, UPDATEs, and DELETEs, but not INSERTs, need to find the row(s); for this, an index may help a lot.

An INSERT is hurt an extra amount if there is a UNIQUE index to check.

Secondary keys (in InnoDB), except for UNIQUE keys, are updated (usually due to INSERT and DELETE, but possibly due to UPDATE) in a 'delayed' way via what is called the "Change Buffer". This effectively puts off updating the index, but still keeps the index fully usable.

None of this is impacted by the order of the columns in an index. However, if an index is bigger than can be cached in RAM, "caching" comes into play, and I/O may or may not be involved. But that is another topic.

In general the benefit from an index for reading far outweighs the slowdown for write operations.

Rick James
  • 135,179
  • 13
  • 127
  • 222
0

Indexes actually slow data modification queries (insert, update, delete) down, since the rdbms has to change not only the table itself, but the index(es) as well.

From an insertion speed point of view, the order of fields in an index is not relevant, it is the number of indexes that matter.

Shadow
  • 33,525
  • 10
  • 51
  • 64
  • Couldn't it be said that *Indexes can speed up some data modification queries (insert,update,delete)*? – Drew Jul 09 '16 at 00:16
  • In case of update and delete when the rdbms has to locate the record to be changed, then an index may speed up the location part and slow down the the change. However, in case of an insert, however, the rdbms does not have to locate any records. – Shadow Jul 09 '16 at 00:35
  • I have some 2 and 3 digit million row tables that delete and update would be impossible for UX without indexes. – Drew Jul 09 '16 at 00:35
  • And what about insert? – Shadow Jul 09 '16 at 07:12
  • Shadow, you seem to think indexes may get you data quicker. But maybe not. I would suggest you pound on huge datasets for a while. It gets very tiresome reading what people have feelings for about how things may work. It is rare people spend the time to prove it. That is pretty much what I do. – Drew Jul 09 '16 at 09:03
  • @Drew I pound on datasets eith several millions of records every day. That's my job. Inserts were only slowed down by indexes, deletes and updates were either slowed down or were unaffected. – Shadow Jul 09 '16 at 10:54
  • So you never found an index to benefit anything versus a table scan. Then ditch your indexes I guess. I'll keep mine :p – Drew Jul 09 '16 at 10:57
  • [Indexes can promote fast updates and deletes](http://i.imgur.com/hBsalet.jpg). I'll take a tenth of a second over 25 seconds anytime. – Drew Jul 09 '16 at 14:50