MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
N.M.P.L. Company www.NearMePayday.Loan || 3-month (90 days)
Powered by MySQL
 
Home / Indexes / Question No: 71

What are best practices to pick columns to index?

I am newbie to MySQL and want to use MySQL indexing feature to optimize my searches. I am not clear that what columns should be indexed in the existing table to improve the performance of SELECT queries? Can you explain, what are best practices to pick columns to index?

Answer No: 71

Indexes can play an important role in query optimization and searching the results speedily from tables. So it is most important step to select which columns 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 to use index */
AND last_name='Iqbal' /* consider to use index */

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 to use index */
WHERE
first_name='Tariq' /* consider to use index */
AND
last_name='Iqbal' /* consider to use index */

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. To learn about indexing disadvantages visit advantages and disadvantages of indexes?
  • 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. To learn more about "multi-colums index" visit When does multi column index come into use?
  • 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.

Related MySQL FAQs to the Above FAQ

How-to-get-existing-indexes-of-the-table-in-MySQL How to get existing indexes of the table in MySQL?

What-is-index-terminology-or-concept-and-how-does-index-work-in-MySQL What is index terminology or concept and how does index work in MySQL?

How-many-types-of-indexes-or-keys-are-in-MySQL How many types of indexes or keys are in MySQL?

What-is-single-column-index-or-key-in-MySQL What is single column index or key in MySQL?

What-is-multi-column-index-or-key-in-MySQL What is multi column index or key in MySQL?

What-is-partial-column-and-prefixed-column-index-or-key-in-MySQL What is partial-column and prefixed-column index or key in MySQL?

When-does-multi-column-index-come-into-use-in-MySQL When does multi column index come into use in MySQL?

What-are-advantages-and-disadvantages-of-indexes-in-MySQL What are advantages and disadvantages of indexes in MySQL?

How-to-remove-or-drop-indexes-in-MySQL How to remove or drop indexes in MySQL?

Is-it-possible-to-apply-more-than-one-keys-on-a-single-column Is it possible to apply more than one keys on a single column?

What-is-foreign-key-in-MySQL What is foreign key in MySQL?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2021  www.mysqlfaqs.net
All rights reserved.