- All
- Engineering
- Database Administration
Powered by AI and the LinkedIn community
1
What is an index?
2
How to analyze a query?
3
How to choose the index type?
4
How to choose the index columns?
5
How to test and monitor the index?
6
How to update and maintain the index?
7
Here’s what else to consider
Indexes are essential for improving the performance of database queries, but how can you determine the best index for a query? In this article, you will learn some basic concepts and steps to help you choose the right index for your database needs.
Top experts in this article
Selected by the community from 136 contributions. Learn more
Earn a Community Top Voice badge
Add to collaborative articles to get recognized for your expertise on your profile. Learn more
- Ali Jafari Noor Oracle DBA Team Lead @ Behsa | Database Optimization
19
- Pilar Salom Viñado DBA SQLServer y PostgreSQL
5
1 What is an index?
An index is a data structure that stores a subset of the columns and rows of a table, organized in a way that makes it easier and faster to find the relevant data. For example, an index can sort the data by a specific column, or create a hash table that maps the values of a column to their locations in the table. Indexes can reduce the amount of disk I/O and memory usage required to execute a query, as well as the number of comparisons and calculations needed.
Help others by sharing more (125 characters min.)
- Ali Jafari Noor Oracle DBA Team Lead @ Behsa | Database Optimization
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Take a look at the end of any book, you can find an appendix that helps you to find the pages that have the keyword that you search. this is the simplest index
LikeLike
Celebrate
Support
Love
Insightful
Funny
19
-
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Index is fuel for queries. We have to use index wisely because good for less data but once data size increased sometimes index not helpful so if possible use table partitions for large tables as index partitions.
LikeLike
Celebrate
Support
Love
Insightful
Funny
4
- Zaheer Abbas Mitaigiri
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Automatic Indexing (DBMS_AUTO_INDEX) in Oracle Database 19cThe automatic indexing feature does the following.Identify potential automatic indexes based on the table column usage. The documentation calls these "candidate indexes".Create automatic indexes as invisible indexes, so they are not used in execution plans. Test the invisible automatic indexes against SQL statements to make sure they give improved performance. If they result in improved performance they are made visible. If performance is not improved, the relevant automatic index is marked as unusuable and later removed. The SQL statements tested against failed automatic indexes are blocklisted, so they will not be considered for automatic indexing in future.
LikeLike
Celebrate
Support
Love
Insightful
Funny
3
-
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Índices ajudam de forma muita efetiva na melhor utilização dos recursos computacionais em base de dados, principalmente aquelas que detém um alto volume de dados. Além disso, a execução de queries em tabelas com índices, bem aplicados, são em sua maioria, muito mais velozes se comparados com a mesma volumetria de dados e colunas.Faz parte das atividades de manutenção executadas por um DBA periodicamente.
Translated
LikeLike
Celebrate
Support
Love
Insightful
Funny
3
-
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
indexes are used to rearrange the table as per the index key id there are mainly two type of indexesclustered and non clustered indexes If the Table in OLTP then it should have indexes where as if the table is OLAP then avoid creating more indexes
LikeLike
Celebrate
Support
Love
Insightful
Funny
2
Load more contributions
2 How to analyze a query?
Before you create an index, you need to analyze the query that you want to optimize. You can use tools like EXPLAIN or SHOW PLAN to see how the database engine executes the query, and what kind of operations and resources it uses. You can also use statistics and metrics to measure the execution time, the number of rows scanned, the number of disk reads and writes, and the CPU usage of the query. By analyzing the query, you can identify the bottlenecks and the opportunities for improvement.
Help others by sharing more (125 characters min.)
- Pilar Salom Viñado DBA SQLServer y PostgreSQL
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Los planes de ejecución hacen lo que pueden, no lo mejor. Para saber qué es lo mejor hay que analizar la query y sus joins, ver cual es el primer dataset por el que conviene empezar según la cardinalidad y asegurarte que hay índices para todas las tablas según el camino que queramos recorrer.A veces el resultado es frustrante, pero en la mayoría de las ocasiones es espectacular.
Translated
LikeLike
Celebrate
Support
Love
Insightful
Funny
5
- Thomas Armstrong Data Analytics Team Lead
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
For a brand new table with a single row/no rows, it can be hard to choose an index as the execution plan may not reveal bottlenecks. Rather than wait for things to slow down, look at your queries and see what you are going to be joining on. For example, a transaction table; you may know in advance that it will be searching for records based on customer ID and date range. So build that as an index in advance before sifting through execution plans. You can always look for unused indexes afterwards.
LikeLike
Celebrate
Support
Love
Insightful
Funny
4
- Vinicius Nogueira Senior DBA Architect | Cloud | Azure | AWS | GCP | noSQL | Migration | Performance
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Before acting on optimizing your query focusing on index/statistics, check its end result and that of the sub-queries first to see if you need:1. All those tens of columns2. All those zillion of rows On DBA consulting, It's not rare that we stumble upon queries pulling out way too much data than needed by current business logic, which can be even harmful for your costs if you're on the cloud.Just by applying this simple and easy technique, you can save a lot on CPU, memory and I/O, thus ultimately speeding up your query to better satisfy your users!
LikeLike
Celebrate
Support
Love
Insightful
Funny
3
- Husnain Nourose Data Analyst | Team Lead BI | Manager | Ex-i2c Inc.
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Start from top of the query, minimize the function calls and extra links with other tables. Try to bring maximum columns and details from main table.
LikeLike
Celebrate
Support
Love
Insightful
Funny
1
-
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
We can analyse a query in many ways. Like in SQL Server we have an option to see the Estimated and Actual plan to see where the query is lacking in performance. There are also many DMVs and DMFs to analyze the same. There is a build in application named perfmon that can record the data to troubleshoot and analyze. There is a process named Query Analyzer in SQL Server to analyze the query. And many more.
LikeLike
Celebrate
Support
Love
Insightful
Funny
Load more contributions
3 How to choose the index type?
There are different types of indexes, such as clustered, non-clustered, unique, primary, secondary, bitmap, full-text, spatial, and so on. Each type has its own advantages and disadvantages, depending on the characteristics of the data and the query. For example, clustered indexes are good for range queries and sorting, but they can increase the overhead of insertions and updates. Non-clustered indexes are good for selective queries and joins, but they can require more disk space and memory. You need to understand the trade-offs and the compatibility of each index type with your database engine and your query.
Help others by sharing more (125 characters min.)
- Ali Jafari Noor Oracle DBA Team Lead @ Behsa | Database Optimization
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
any kind of index has its pros and cons. so be aware of the type of index and the operations on the related table. the index can help you to find the data, but can impact the DML performance. Btree or normal indexes always are the first place to choose, but do not ignore the power of composite indexes.Bitmap indexes mostly are a good choice for OLAP systems if you have a lower NDV than 5 percent of row numbers. in conclusion, get to know all types of indexes in your database platform.
LikeLike
Celebrate
Support
Love
Insightful
Funny
5
- Fatoumatou Yaou Web developer at Ifutur | Linkedin Top Voice🏆 | PHP fullstack developer | DBA
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Les index facilitent certes une localisation rapide des informations spécifiques ce qui nous permet d’optimiser nos requêtes. Cependant leurs utilisations quelque soit le type d’index entraîne une charge supplémentaire surtout lors de la mise à jour des données. Voilà pourquoi il est important de judicieusem*nt choisir les colonnes à indexer en fonction des types de requêtes fréquemment utilisées.
Translated
LikeLike
Celebrate
Support
Love
Insightful
Funny
2
-
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Leverage Partial Indexes:- Target Specific Data Subsets: Optimize queries that focus on a portion of table data by creating partial indexes that encompass only rows meeting certain conditions.- Example: A partial index on active_users where status = 'active' can speed up queries targeting those active users.
LikeLike
Celebrate
Support
Love
Insightful
Funny
1
-
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Choosing index types involves considering:Query patterns: B-trees for range queries, hashes for exact matches.Column cardinality: High for B-trees, low for Bitmaps.Data access patterns: Balance read and write efficiency.System specifics: Hardware and database support.Experiment, test, and monitor for optimal performance.
LikeLike
Celebrate
Support
Love
Insightful
Funny
1
-
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
There are many factors we have to consider before choosing the index. Like Query patters, Data Characteristics, write operations etc.There are many types of indexes available. But the most commonly used are Clustered and Non-Clustered Index. Other indexes are Unique, Composite etc.
LikeLike
Celebrate
Support
Love
Insightful
Funny
Load more contributions
4 How to choose the index columns?
The columns that you include in the index can have a significant impact on the query performance. You want to choose the columns that are most frequently used in the query conditions, such as the WHERE, JOIN, ORDER BY, and GROUP BY clauses. You also want to choose the columns that have high cardinality, meaning that they have many distinct values and low repetition. This can increase the selectivity and the efficiency of the index. You can also consider the data type and the size of the columns, as some types are more suitable for indexing than others.
Help others by sharing more (125 characters min.)
- Ali Jafari Noor Oracle DBA Team Lead @ Behsa | Database Optimization
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
for composite indexes, if you have any doubt that the queries always filter all the columns of index, try to choose the columns if lower to higher NDV sort
LikeLike
Celebrate
Support
Love
Insightful
Funny
5
- Ajimon Yohannan Senior Manager SAP
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Analyze the queries and understand the columns that are frequently used in the WHERE, JOIN, ORDER BY, and GROUP BY clauses to consider for indexing. Also, columns with high selectivity and columns that are included in joins between multiple tables are also consider for indexing.
LikeLike
Celebrate
Support
Love
Insightful
Funny
2
- Roberto Freire Senior Database Specialist
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Seek to use columns more selective because selectivity is a key point to develop proper indexes where data from large tables may be easily returned once that you are using a selective column and no table scan is executed by the database to return the desired data.
LikeLike
Celebrate
Support
Love
Insightful
Funny
2
- Fernando A. Girón Jiménez Desarrollo de Nuevos Negocios
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Se deben elegir en función de los reportes y análisis que se realice de la información contenida en la tabla, suele pasar que se toman como índices algunas llaves primarias que no necesariamente se usan en los reportes.
Translated
LikeLike
Celebrate
Support
Love
Insightful
Funny
1
- Bevan Ward Principal Advisor Innovation - Data and Information Management at Rio Tinto
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
The key is to understand well the datamodel such that a proper tables/views can be joined as intended otherwise the result is likely wrong. Typically key fields for indexing will be join fields - primary/composite and foreign keys. As the model is used more note key required queries and extending indexes to cover any field used in where statements, case statements, sorting etc. As the model is better used it may be required to refactor to improve queryability. If indexing is not improving overall performance.
LikeLike
Celebrate
Support
Love
Insightful
Funny
Load more contributions
5 How to test and monitor the index?
After you create an index, you need to test and monitor its effect on the query performance. You can use the same tools and metrics that you used to analyze the query, and compare the results before and after the index creation. You can also use tools like INDEX TUNING WIZARD or DATABASE TUNING ADVISOR to get recommendations and feedback on your index design. You should also monitor the impact of the index on the other aspects of the database, such as the storage space, the maintenance cost, the concurrency, and the workload.
Help others by sharing more (125 characters min.)
-
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Continuously Evaluate and Adapt:- As data and query patterns evolve, periodically review index effectiveness and make adjustments or additions as needed.- Regularly monitor query performance and resource utilization to identify potential index-related improvements.
LikeLike
Celebrate
Support
Love
Insightful
Funny
3
- Bevan Ward Principal Advisor Innovation - Data and Information Management at Rio Tinto
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
If you are able to display an estimated execution plan you can clearly see where the majority of the execution load falls. This will help re-writing or discussing with a DBA key indexing missing or observing indexing being leveraged for effective execution. It is important to not simply add a new index given a query as it may be optimal to restructure the query, add an execution hint, or even create a temporary table/table variable as an intermediate data state to improve overall execution time. I've been able to improve queries time to subsecond from many minutes by taking this approach.
LikeLike
Celebrate
Support
Love
Insightful
Funny
3
- Víctor Ramón Centurión Casadevall Sr. Database Administrator at Gartner
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Make sure to review the execution plan before and after as well as overall impact as it might come with unexpected plan changes in other queries as well.
LikeLike
Celebrate
Support
Love
Insightful
Funny
2
- Benoît Leroux Artiste peintre
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Il faut considérer qu'un nombre trop élevé d'index sur les même tables peuvent dégrader considérablement la performance des requètes de modification sur la base de donnée. Ceci parceque tous ces indexes doivent aussi être mits à jour lors de ces requêtes.
Translated
LikeLike
Celebrate
Support
Love
Insightful
Funny
1
- Mohsen Arsanjani Senior Software Developer at Geeks Ltd
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Profiler and filter queries that have a high execution timeUse most expensive queries list in management studioGet the execution plan
LikeLike
Celebrate
Support
Love
Insightful
Funny
Load more contributions
6 How to update and maintain the index?
Creating an index is not a one-time task. You need to update and maintain the index as the data and the query change over time. You can use tools like REINDEX or REBUILD to refresh the index and remove any fragmentation or corruption. You can also use tools like DROP or DISABLE to remove or deactivate the index if it becomes obsolete or redundant. You should also review and revise your index design periodically, and follow the best practices and guidelines of your database engine and your query.
Help others by sharing more (125 characters min.)
-
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
We have to perform the regular maintenance of indexes.Like in SQL server we have DMVs to check the index fragmentation and based on the same we can perform the index reorganize & index rebuild.As per the best practice, if the fragmentation is between 5% to 30%, we can do the reorganize and if the fragmentation is more than 30%, we should do the index rebuild.
LikeLike
Celebrate
Support
Love
Insightful
Funny
2
- Thomas Armstrong Data Analytics Team Lead
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Search for Ola Hallengren. Brent Ozar is a good resource as well. Microsoft is a good place for Azure SQL and Azure Synapse. These people have created really effective index management processes which you can customise but work well out of the box. These processes will look for indexes which are fragmented and working inefficiently and take steps to fix them.
LikeLike
Celebrate
Support
Love
Insightful
Funny
- Miguel Ángel Badillo Tivo Licenciado en Sistemas Computacionales y Administrativos | Desarrollador | PHP | HTML | LARAVEL | DOCKER | Postman | Full Stack | BBD | MySQL | PostgreSQL | Bootstrap | CSS | JavaScript | AJAX | JSON | Query | JQuery
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
1. Estadísticas y Monitoreo2. Programación de Mantenimiento3. Reorganización de Índices4. Reconstrucción de Índices5. Fragmentación de Índices6. Eliminación de Índices No Utilizados7. Particionamiento de Índices8. Estudio de Planes de Ejecución de Consultas9. Pruebas y Monitoreo Continuo10. Realización de Operaciones en Períodos de Baja Carga
Translated
LikeLike
Celebrate
Support
Love
Insightful
Funny
-
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Rebuilding the index may degrade the performance of the OLTP. Do it during idle period to degrading the database performance.
LikeLike
Celebrate
Support
Love
Insightful
Funny
- Dr Greg Low
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Always fascinates me to see the percentages used in isolation. The way the indexes are used (mostly seek vs scan) determines s great deal about what type of maintenance is needed.
LikeLike
Celebrate
Support
Love
Insightful
Funny
Load more contributions
7 Here’s what else to consider
This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?
Help others by sharing more (125 characters min.)
-
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Consider Covering Indexes. Covering indexes encompass all columns required by a query, eliminating the need to access the underlying table data, significantly boosting performance. e.g., for a query that frequently retrieves only name and email from a users table, a covering index on those two columns can dramatically speed up results.Explore Function-Based Indexes. Function-based indexes create indexes on expressions or functions applied to columns, enabling efficient retrieval of data based on computed values. e.g., for frequent queries involving a upper(name) comparison, a function-based index on upper(name) can accelerate such searches.
LikeLike
Celebrate
Support
Love
Insightful
Funny
2
- Bevan Ward Principal Advisor Innovation - Data and Information Management at Rio Tinto
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
It is always worth considering rewriting queries to make use of temporary tables to improve performance, reduce complexity and readability. Also consider artificial keys/surrogates where otherwise a very large text field needs to be included.
LikeLike
Celebrate
Support
Love
Insightful
Funny
1
- Chris Grobauskas, ChFC® Senior Technology Engineer at State Farm
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
For many databases, there are statistics tables that track database activity, including index usage.If you find unused indexes, you should consider dropping them if they are not enforcing uniqueness.For Postgres, look at the last_idx_scan column in the pg_stat_user_indexes catalog view.With all of these sources, make sure to:- Read the manual to know any exceptions to how statistics are collected.- Keep in mind, these statistics may be reset either intentionally by DBAs, certain commands, or maintenance utilities.- Some indexes are only used infrequently.- Unique Indexes may be in place to enforce uniqueness even if they are not used in selects.
LikeLike
Celebrate
Support
Love
Insightful
Funny
1
- Angel soriano Backend Developer | NodeJS | Express | Javascript | Typescript | SQL | JIRA | SCRUM | Postman | BASH Script
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
la gestión de índices es un proceso continuo que requiere atención y ajustes periódicos para garantizar un rendimiento óptimo de las consultas. La variedad de opiniones refleja la complejidad de este proceso y destaca la importancia de adaptarse a las necesidades específicas de la base de datos y las consultas.
Translated
LikeLike
Celebrate
Support
Love
Insightful
Funny
- Roberto Freire Senior Database Specialist
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Always remember the best queries are selective, so it is very important to develop selective queries because you will get to improve the query performance and save money once that queries with good performance take less time to complete and consume less resources what means a low cost query.
LikeLike
Celebrate
Support
Love
Insightful
Funny
Load more contributions
Database Administration
Database Administration
+ Follow
Rate this article
We created this article with the help of AI. What do you think of it?
It’s great It’s not so great
Thanks for your feedback
Your feedback is private. Like or react to bring the conversation to your network.
Tell us more
Tell us why you didn’t like this article.
If you think something in this article goes against our Professional Community Policies, please let us know.
We appreciate you letting us know. Though we’re unable to respond directly, your feedback helps us improve this experience for everyone.
If you think this goes against our Professional Community Policies, please let us know.
More articles on Database Administration
No more previous content
- A team member mistakenly erases vital database files. How will you recover from this data disaster?
- Here's how you can select the right continuing education program as a database administrator.
- You're facing high-traffic performance issues. How do you maintain operations without disruption?
- You're facing team tensions in data recovery chaos. How do you smoothly resolve conflicts under pressure?
- You're dealing with software compatibility challenges. How do you decide which conflicts to tackle first?
- Here's how you can enhance your interactions as a Database Administrator with empathy.
No more next content
Explore Other Skills
- Programming
- Web Development
- Machine Learning
- Software Development
- Computer Science
- Data Engineering
- Data Analytics
- Data Science
- Artificial Intelligence (AI)
- Cloud Computing
More relevant reading
- Database Queries How do you choose the optimal level of normalization for your database queries?
- Business Intelligence What strategies can improve query efficiency in large databases?
- Database Development What is the best index type for optimizing database queries?
- Data Architecture What are the most effective ways to optimize DB2 query response time?