A good Database Administrator (DBA) can create and maintain a great database on an efficient server. The problem is other people use the database, like developers that are making changes so that they can write programs to access that perfect database. As those changes are made, the database can become less efficient if the proper techniques aren’t used by the development team. In this article by Rodrigo Koch, he helps us understand the techniques developers should use.
If you’re a complete newcomer to databases, you should know that indexing is an effective way to tune your database that is often neglected during development. In basic terms, an index is a data structure that improves the speed of data retrieval operations on a database table by providing rapid random lookups and efficient access of ordered records. This means that once you’ve created an index, you can select or sort your rows faster than before.
Indexes are also used to define a primary-key or unique index which will guarantee that no other columns have the same values. Of course, indexing is a vast an interesting topic to which I can’t do justice with this brief description.
If you’re new to indexes, I recommend using this diagram when structuring your queries: ￼
Basically, the goal is to index the major searching and ordering columns.
Note that if your tables are constantly hammered by
DELETE, you should be careful when indexing—you could end up decreasing performance as all indexes need to be modified after these operations.
Further, DBAs often drop their indexes before performing million+ row batch inserts to speed up the insertion process. After the batch is inserted, they then recreate the indexes. Remember, however, that dropping indexes will affect every query running in that table; so this approach is only recommended when working with a single, large insertion.