I recently watched the following video by Stephane Faroult in which he talks about the basics of SQL indexing.



Below is a brief summary of what he discusses in the video. This (in addition to the video) should give you a very basic fundamental understanding of what SQL indexing means and why it is important.

An index entry is the association of a key value with a physical address. All entries are stored in order of key value.

Do we need a full index or just a chapter list?
A composite index (i.e. year/country) is a concatenation of the two in which we call the result the key. So if the composite index is Country|Year the WHERE clause must be on country, and vice versa.

Indexes on performance:
100,000 Rows, 12 Columns
No Index: 100 inserts
Prim Key: 65 inserts
2 Indexes: 22 inserts
3 Indexes: 15 inserts
4 Indexes: 5 inserts

As you can see, by adding indexes we are taking a big hit in performance (not to mention storage as well). In the same time it takes to insert 100 records with no index, we can only insert 22 records when we have just 2 indexes.

Model for database architecture:
1 – Production
1b – Mirror
2 – Disaster Recovery Site
2b – Mirror
3 – Development
4 – Development
5 – User Acceptance Test bed
6 – Performance Testing

-Nicholas

Read Users' Comments (0)