Database indexes – Few basic points
I used MS Sql Server, Oracle in my past projects and currently using MySql. Here i am trying to list down few points regarding indexes. These can be quite basic and elementary but practically really very effective, Trying to list what i learned over time:
1. Create index on columns which are frequently used for WHERE, ORDER BY and GROUP BY. In case you are doing ORDER BY on multiple column of a bigger table and your require some limited record as a result of that query, use LIMIT with it.
2. Keep your indexes as simple as possible. As index creation takes its own space, keep index narrow. It will also improve the index performance.
3. Do not keep non-used indexes and drop them as their use is finished. As index takes its space and also a cost in case of update, insert and delete in a table, should not keep them if not used.
4. Create index on database ids i.e. integer values and avoid indexes on character values as it takes more space and reduce performance.
5. Be careful and limit your indexes in case you update, create data frequently. Choose the trade off for new indexes carefully. For read-only table, number of indexes can be increased.
6. There are some indexes which are created automatically like unique keys and primary keys. Do not create index for them again.
7. Don’t create index on column which have very less unique values For example, don’t create an index for columns with many duplicate values, such as “Status” column (which has only “New”, “Active” and “Expired” values), because in this case the disadvantages of additional space used and slowly rows modification outweigh the speed advantages of creating a new index.
8. If you need to join several tables very frequently, consider creating index on the joined columns.
This can significantly improve performance of the queries against the joined tables.
Most Commented Posts
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.
Comments
No comments yet.
Leave a comment