The first index makes the greatest difference.
To optimize insert
performance, it is very important to keep the number of indexes small.
Use indexes deliberately and sparingly, and avoid redundant indexes whenever possible. This is also beneficial for delete
and update
statements.
Considering insert
statements only, it would be best to avoid indexes entirely—this yields by far the best insert
performance. However tables without indexes are rather unrealistic in real world applications. You usually want to retrieve the stored data again so that you need indexes to improve query speed. Even write-only log tables often have a primary key and a respective index.
Nevertheless, the performance without indexes is so good that it can make sense to temporarily drop all indexes while loading large amounts of data—provided the indexes are not needed by any other SQL statements in the meantime. This can unleash a dramatic speed-up which is visible in the chart and is, in fact, a common practice in data warehouses.
How would Figure 8.1 change when using an index organized table or clustered index?
Is there any indirect way an insert
statement could possibly benefit from indexing? That is, could an additional index make an insert
statement faster?
You can’t learn everything in one day. Subscribe the newsletter via E-Mail, Bluesky or RSS to gradually catch up. Have a look at modern-sql.com as well.
The essence of SQL tuning in 200 pages
Buy now!
(paperback and/or PDF)
Paperback also available at Amazon.com.
Markus offers SQL training and consulting for developers working at companies of all sizes.
Learn more »