Indexes are great. They speed up our queries. In fact, without them relational database systems wouldn’t work.
Different indexes work best for different queries. In a system with a lot of queries that means we could need a lot of indexes. Fortunately we can have up to 999 non-clustered indexes per table and one clustered for a total of 1000 indexes. That’s a huge amount. So should we have as many indexes as possible, just in case right?
Let’s just go with no. Indexes are awesome and make queries much faster. Read queries. Not write queries. In fact they slow down write queries. Not significantly, but it is enough to make a difference. This means that OLAP (online analytical processing, data warehouses for example) databases can have a lot more indexes than OLTP (online transaction processing) database. They do significantly less writing than reading. But what kind of impact are we talking about?
Discussion here, code below
First we are going to create a nice wide table. Say 100 columns wide. Then we are going to run three tests. The first with only a clustered index (the primary key in this case). The second with a single additional index and the third with 100 total indexes. Each time I’m going to insert 100,000 rows.
- CI Only – 12130 ms.
- CI + 1NCI – 14057 ms.
- CI + 99NCI – 126876 ms.
So about 2 extra seconds for the first non-clustered index. Once we got up to 99 non-clustered it only averaged about 1.1 seconds per index. Still, in the world of databases that’s a lot of time.
Now I’m guessing at least a few of you are thinking “Well, that’s a lot of rows. If I’m only inserting one or two at a time it won’t make that big a difference.” Well, maybe true, maybe not. One insert at a time a hundred times a minute is going to really add up. One insert a day? Probably not a problem. Of course there are a couple of other considerations. First of all there is maintenance. The more indexes the longer your index maintenance will take. Then there is space. Indexes take up space (obviously). A lot of indexes will take up a lot of space and could really bloat your database.
The single NCI added ~4MB while the 99 indexes added ~600MB and more than doubled the size of the table.
I want to make a couple of final points. I realize 99 indexes is a lot. It’s to emphasize the differences. However they were also fairly small indexes and this is a single table where a normal database might easily have hundreds. So take these results as an example. They aren’t going to match real life but will hopefully show you how all of this can play out.
Indexes are awesome but you want to be smart about adding them. My personal rule of thumb, with no scientific evidence behind it, is 5 indexes or less and I’m pretty easy. 5-10 indexes and you’ll have to convince me. I’m going to be reviewing the existing indexes and see what I can get rid of, or maybe I can combine something. Past 10 indexes and it had best be for a query that’s running a 100+ times a minute or something for the CEO.
Continue reading on SQLStudies.com.