Indexing your data is so broad that it's hard to know what to do and what not to do when developing your indexing strategies. You will agree with me that if you create indexes it is to improve the response time of your requests or applications, but indexing data is a balancing act.
Each index is a table that's managed by the system, so each addition or modification to the data in a table of your application potentially implies, as already mentioned in this book, the updating of these indexes, which can slow the performance of the updates to the data files.
The important points that you need to consider in your indexing strategy are as follows:
- Create a primary key (generally, the column will end with id)
- Predict the columns that will often be queried in your application with the WHERE, GROUP BY, HAVING, and ORDER BY clauses
- You...