How to Optimize Query Performance in MySQL Databases

As you gain experience writing queries in MySQL, you will come to understand how to design schemas to support efficient queries. Similarly, what you learn about optimal schema design will influence the kinds of queries you write. This process takes time, so we encourage you to refer back to this chapter and the previous one as you learn more.

Like other data dictionary tables, this table is not directly accessible by users. Instead, you can obtain histogram information by querying INFORMATION_SCHEMA.COLUMN_STATISTICS, which is implemented as a view on the data dictionary table. You can also perform histogram management using the ANALYZE TABLE statement.

Exclusive MySQL Performance Tuning Tips For Better Database Optimization

If the query executes again, then the optimizer uses the performance statistics gathered during the initial execution to better determine a degree of parallelism for the statement. At the end of execution, the optimizer compares its initial cardinality estimates to the actual number of rows returned by each operation in the plan during execution. If estimates differ significantly from actual cardinalities, then the optimizer stores the correct estimates for subsequent use. The optimizer also creates a SQL plan directive so that other SQL statements can benefit from the information obtained during this initial execution.

• MySQL can’t do true hash joins at the time of this writing—everything is a nested-loop join.
• Query optimization, index optimization, and schema optimization go hand in hand.
• You can do a web search and find more misinformation on this topic than we care to think about.
• See the queries below, one is using a leading wildcard and another one is using an ending wildcard.
• Many of these rows could be eliminated by a WHERE clause and end up not contributing to the result set.

If you aren’t getting a good access type, the best way to solve the problem is usually by adding an appropriate index. We discussed indexing at length in the previous chapter; now you can see why indexes are so important to query optimization. Indexes let MySQL find rows with a more efficient access type that examines less data. However, like execution time, it’s not a perfect metric for finding bad queries. Shorter rows are faster to access, and fetching rows from memory is much faster than reading them from disk. All three metrics are logged in the slow query log, so looking at the slow query log is one of the best ways to find queries that examine too much data. This presentation will introduce you to the inner workings of the MySQL Query Optimizer by showing you examples with Optimizer Trace.

Rows examined and access types

This is O in the size of the list, whereas an equivalent series of OR clauses is O in the size of the list (i.e., much slower for large lists). UseSTRAIGHT_JOIN to force the optimizer to use tables in a particular order. If you do this, you should order the tables so that the first table is the one from which the smallest number of rows will be chosen. If you are not sure which table this is, put the table with the greatest number of rows first. In other words, try to order the tables to cause the most restrictive selection to come first.

A Memcached layer reduces the number of times the database makes a request. In some scenarios, adding memory becomes highly substantial for improving performance when it comes to the magnitude. It does look a bit counterintuitive, but in most cases, the overutilization of disks affects directly to the database performance. As the deficiency of enough memory to hold the server’s data proves costly in derailing database performance. They serialize your workload, preventing completion of tasks in parallel, and they often result in a table that contains work in process as well as historical data from already completed jobs. It not only adds latency to the application but also adds hindrance to the MySQL performance tuning.

The next step: schema optimization and indexing

For the first line, no index is used because the column must be retrieved for each row so that the value of TO_DAYS can be computed. Both cutoff andTO_DAYS(CURDATE()) are constants, so the right-hand side of the comparison can be calculated by the optimizer once before processing the query, rather than once per row. But the date_col column still appears in a function call, preventing use of the index. Again, the right-hand side of the comparison can be computed once as a constant before executing the query, but now the value is a date. That value can be compared directly to date_col values, which no longer need to be converted to days. Try to make indexed columns stand alone in comparison expressions. If you use a column in a function call or as part of a more complex term in an arithmetic expression, MySQL can’t use the index because it must compute the value of the expression for every row.

• The estimator can derive cardinality from the table statistics collected by DBMS_STATS, or derive it after accounting for effects from predicates , DISTINCT or GROUP BY operations, and so on.
• The optimizer chooses the degree of parallelism based on the estimated performance of the statement.
• According to this MySQL developers guide, you can be proactive and plan for optimizations or troubleshoot queries and configurations after experiencing problems.
• The size of the bubble roughly corresponds to the impact of the feature .
• The Query Execution Engine executes the plan by making calls to the Storage engine through special handler interfaces.
• On rare occasions it is necessary to send out a strictly service related announcement.
• The histogram captures the distribution of different values in a column, so it yields better selectivity estimates, especially for columns that have data skew.

For example, during query optimization, when deciding whether the table is a candidate for dynamic statistics, the database queries the statistics repository for directives on a table. If the query joins two tables that have a data skew in their join columns, a SQL plan directive can direct the optimizer to use dynamic statistics to obtain an accurate cardinality estimate.

The Query Execution Engine

They are characters used to help search for data that match complex criteria. The only exception is if the data changes frequently or when you need your queried information to be up-to-date every time. There used to be an in-built Cache layer in MySQL called “The Query Cache,” but it was deprecated for scalability reasons. Luckily, some tools can help cache data, such as Memcached and Redis. Whenever you send a query to the MySQL server, you send a series of instructions that you want it to perform. The Query Execution Engine executes the plan by making calls to the Storage engine through special handler interfaces.

We will also show how optimizer trace gives you insight into the cost model and how the optimizer does cost estimations. Help the optimizer make better estimates about index effectiveness.

The presentation will also cover tools that can be used to help process the vast amount of information in an optimizer trace. If you were alphabetizing people by their last name, you could use a “logical bucket” for the folks with last names starting with the letters A to F, then another for G to J, and so forth.

• Recall that the optimizer doesn’t account for this cost—it optimizes just the number of random page reads.
• If the server uses this optimization, you’ll see “Select tables optimized away” in the EXPLAIN plan.
• If the two values vary significantly, then the database marks the statement for reparsing, and stores the initial execution statistics as feedback.
• You can also install the performance sensor for ongoing performance insights.
• This can make it “underprice” the query, which might in fact run more slowly than a plain table scan.

This means fewer tuples will fit into the sort buffer, and the filesort will have to perform more sort merge passes. A query can often be executed many different ways and produce the same result.

The execution plan

If you omit the ALL keyword, MySQL adds the distinct option to the temporary table, which uses the full row to determine uniqueness. Be aware that the ALLkeyword doesn’t eliminate the temporary table, though. MySQL always places results into a temporary table and then reads them out again, even when it’s not really necessary . Make sure there are indexes on the columns in the ON or USING clauses. See “Indexing Basics” on Indexing Basics for more about indexing. If you’re joining tables A and B on column c and the query optimizer decides to join the tables in the order B, A, you don’t need to index the column on table B. In general, you need to add indexes only on the second table in the join order, unless they’re needed for some other reason.

To update an index, an ANALYZE TABLE command must be updated. This is a good approach when the data does not churn very much and frequent changes to the data will reduce the efficiency. California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. If a user’s personally identifiable information changes , we provide a way to correct or update that user’s personal data provided to us.