What are some best practices and “rules of thumb” for creating database indexes?

by

Some of my rules of thumb:

  • Index ALL primary keys (I think most RDBMS do this when the table is created).
  • Index ALL foreign key columns.
  • Create more indexes ONLY if:
    • Queries are slow.
    • You know the data volume is going to increase significantly.
  • Run statistics when populating a lot of data in tables.

If a query is slow, look at the execution plan and:

  • If the query for a table only uses a few columns, put all those columns into an index, then you can help the RDBMS to only use the index.
  • Don’t waste resources indexing tiny tables (hundreds of records).
  • Index multiple columns in order from high cardinality to less. This means: first index the columns with more distinct values, followed by columns with fewer distinct values.
  • If a query needs to access more than 10% of the data, a full scan is normally better than an index.

More Related Contents:

  1. Is storing a delimited list in a database column really that bad?
  2. Should each and every table have a primary key?
  3. What’s the difference between identifying and non-identifying relationships?
  4. Database, Table and Column Naming Conventions? [closed]
  5. Can I have multiple primary keys in a single table?
  6. How are super- and subtype relationships in ER diagrams represented as tables?
  7. Normalization: What does “repeating groups” mean?
  8. Difference between scaling horizontally and vertically for databases [closed]
  9. Relational table naming convention [closed]
  10. Best practices for storing postal addresses in a database (RDBMS)?
  11. When I should use one to one relationship?
  12. Why use a 1-to-1 relationship in database design?
  13. What is the optimal length for an email address in a database?
  14. DOs and DONTs for Indexes [closed]
  15. Database optimization orders
  16. In what way does denormalization improve database performance?
  17. Best representation of an ordered list in a database?
  18. Alternatives to Entity-Attribute-Value (EAV)?
  19. have address columns in each table or an address table that is referenced by the other tables?
  20. Minimum no of tables that exists after decomposing relation R into 1NF?
  21. PostgreSQL Database Log Transaction
  22. Limit an sqlite Table’s Maximum Number of Rows
  23. NULL permitted in Primary Key – why and in which DBMS?
  24. When we need to use 1-to-1 relationship in database design?
  25. What’s the better database design: more tables or more columns?
  26. Should OLAP databases be denormalized for read performance? [closed]
  27. Best beginner resources for understanding the EAV database model? [closed]
  28. Derived account balance vs stored account balance for a simple bank account?
  29. In a StackOverflow clone, what relationship should a Comments table have to Questions and Answers?
  30. uncertainty in developing a database model

Leave a Comment