MySQL query without index

Background

Database servers can process thousands of queries per second, which are a central part of many applications. Optimizing these queries shortens their execution time and improves query throughput.

To answer the query, the database server must find data in tables. Ideally, a so-called index is used for this purpose, which can be thought of as an ordered index in a dictionary. This index is very small in comparison to the dictionary, uses a binary search tree and through the alphabetical order the pages of the words can be found quickly. The absence of such an index can result in the need to search all rows of the tables involved in the query. Since the indexes are very small in comparison, it is (almost) always worthwhile to create missing indexes subsequently if necessary.

Solution

To check how a query is executed, the MySQL commands EXPLAIN or EXPLAIN ANALYZE can be placed before the query.

With the command SHOW INDEX FROM #Tabellenname#;it is possible to check which indexes are currently available for a table.

If the result shows that an index can be inserted here, it can be created with the CREATE INDEX command. The result of MySQL queries is not changed by existing indexes. Therefore, you can easily insert an index for applications that have been developed externally.

To find MySQL queries that can be optimized, the use of a profiler can be helpful. Especially for queries that have a long execution time, it should be checked whether an index is used for all tables.

It is also possible to determine from the Performance Scheme which queries are executed without an index. The following command can be used for this purpose:

SELECT SCHEMA_NAME, DIGEST, DIGEST_TEXT, SUM_TIMER_WAIT/1000000000000 SUM_TIMER_WAIT_SEC FROM performance_schema.events_statements_summary_by_digest where last_seen > (date(now()) - Interval - 5 minute) AND SCHEMA_NAME IS NOT NULL AND SCHEMA_NAME NOT IN("information_schema", "mysql", "performance_schema") and DIGEST_TEXT not like "SHOW%" AND SUM_NO_INDEX_USED > 0 order by (sum_timer_wait_sec) desc limit 5 \G

Our new ShopPerformance tool can also display corresponding queries.