We should use indexes for frequently used queries on big tables because index functionality is one of the best ways to improve the read performance of a database schema. Index entries are stored in a sorted order, which helps when processing the GROUP BY and ORDER BY clauses. Without an index, a database has to perform sort operations at the time of query executions. Through indexes, we can minimize the query execution time and improve query performance, but we should take care when creating indexes on a table; there are certain drawbacks, as well.

We should not create too many indexes on tables that update frequently because, at the time of any data modification on the table, indexing is also changed. We should use a maximum of four to five indexes on a table. If the table is read-only, then we can add more indexes without worrying.

Here are the guidelines to build the most effective indexes for your application, which are valid for every database:

• In order to achieve the maximum benefits of indexes, we should use indexes on appropriate columns. Indexes should be used on those columns that are frequently used in WHERE, ORDER BY, or GROUP BY clauses in queries.
• Always choose integer data type columns for indexing because they provide better performance than other data type columns. Keep indexes small because short indexes are processed faster in terms of I/O.
• Clustered indexes are usually better for queries retrieving a range of rows. Non-clustered indexes are usually better for point queries.