What columns generally make good indexes?

by

Indexes can play an important role in query optimization and searching the results speedily from tables. The most important step is to select which columns are to be indexed. There are two major places where we can consider indexing: columns referenced in the WHERE clause and columns used in JOIN clauses. In short, such columns should be indexed against which you are required to search particular records. Suppose, we have a table named buyers where the SELECT query uses indexes like below:

SELECT
 buyer_id /* no need to index */
FROM buyers
WHERE first_name="Tariq" /* consider indexing */
AND last_name="Iqbal"   /* consider indexing */

Since “buyer_id” is referenced in the SELECT portion, MySQL will not use it to limit the chosen rows. Hence, there is no great need to index it. The below is another example little different from the above one:

SELECT
 buyers.buyer_id, /* no need to index */
 country.name    /* no need to index */
FROM buyers LEFT JOIN country
ON buyers.country_id=country.country_id /* consider indexing */
WHERE
 first_name="Tariq" /* consider indexing */
AND
 last_name="Iqbal" /* consider indexing */

According to the above queries first_name, last_name columns can be indexed as they are located in the WHERE clause. Also an additional field, country_id from country table, can be considered for indexing because it is in a JOIN clause. So indexing can be considered on every field in the WHERE clause or a JOIN clause.

The following list also offers a few tips that you should always keep in mind when intend to create indexes into your tables:

  • Only index those columns that are required in WHERE and ORDER BY clauses. Indexing columns in abundance will result in some disadvantages.
  • Try to take benefit of “index prefix” or “multi-columns index” feature of MySQL. If you create an index such as INDEX(first_name, last_name), don’t create INDEX(first_name). However, “index prefix” or “multi-columns index” is not recommended in all search cases.
  • Use the NOT NULL attribute for those columns in which you consider the indexing, so that NULL values will never be stored.
  • Use the –log-long-format option to log queries that aren’t using indexes. In this way, you can examine this log file and adjust your queries accordingly.
  • The EXPLAIN statement helps you to reveal that how MySQL will execute a query. It shows how and in what order tables are joined. This can be much useful for determining how to write optimized queries, and whether the columns are needed to be indexed.

Update (23 Feb’15):

Any index (good/bad) increases insert and update time.

Depending on your indexes (number of indexes and type), result is searched. If your search time is gonna increase because of index then that’s bad index.

Likely in any book, “Index Page” could have chapter start page, topic page number starts, also sub topic page starts. Some clarification in Index page helps but more detailed index might confuse you or scare you. Indexes are also having memory.

Index selection should be wise. Keep in mind not all columns would require index.

More Related Contents:

  1. Multiple Indexes vs Multi-Column Indexes
  2. Best way to store time (hh:mm) in a database
  3. Database design: Calculating the Account Balance
  4. Date range overlapping check constraint
  5. What do Clustered and Non-Clustered index actually mean?
  6. Storing a file in a database as opposed to the file system?
  7. How to create multiple one to one’s
  8. Storing JSON in database vs. having a new column for each key
  9. SQL Server indexes – ascending or descending, what difference does it make?
  10. How to create materialized views in SQL Server?
  11. Why use the INCLUDE clause when creating an index?
  12. Composite Primary key vs additional “ID” column?
  13. What’s the longest possible worldwide phone number I should consider in SQL varchar(length) for phone
  14. SQL Server: Index columns used in like?
  15. Getting execute permission to xp_cmdshell
  16. Should every User Table have a Clustered Index?
  17. TSQL: Create a view that accesses multiple databases
  18. SQL Unique constraint across multiple tables
  19. “Cannot open user default database. Login failed.” after installing SQL Server Management Studio Express
  20. How to create a yes/no boolean field in SQL server?
  21. Executing set of SQL queries using batch file?
  22. How to store directory / hierarchy / tree structure in the database?
  23. What are some best practices and “rules of thumb” for creating database indexes?
  24. What are the advantages of VistaDB
  25. Generate table relationship diagram from existing schema (SQL Server) [closed]
  26. What are the limitations to SQL Server Compact? (Or – how does one choose a database to use on MS platforms?)
  27. How do you deal with blank spaces in column names in SQL Server?
  28. Unique identifier (guid) as primary key in database design
  29. Single or multiple databases
  30. Why would using a temp table be faster than a nested query?

Leave a Comment