How can you determine the best index for a query? (2024)

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

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.

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.

Í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

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

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

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.

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!

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.

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.

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.

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

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.

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.

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.

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

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.

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.

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

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.

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.

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.

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.

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

Profiler and filter queries that have a high execution timeUse most expensive queries list in management studioGet the execution plan

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.

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.

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

Rebuilding the index may degrade the performance of the OLTP. Do it during idle period to degrading the database performance.

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.

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.

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.

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.

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

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.