There are several limitations to how indexes are used and managed in MySQL in comparison to other RDBMS products. Cost Based Optimizer MySQL uses a cost based optimizer to prune the possible query tree to create the most optimal SQL execution path. MySQL has limited capabilities for using generated statistics to aid the optimizer as […]
Read More
Although adding indexes can help to optimize the performance of SQL statements, the addition of indexes comes at a significant cost. DML Impact Adding indexes to a table affects the performance of writes. This can be easily shown from the artist table used in this chapter. Looking at the current definition shows a large number of […]
Read More
MySQL has a small number of query hints that can affect performance. There are hints that affect the total query and those that affect how individual table indexes are used. Total Query Hints All of the total query hints occur directly after the SELECT keyword. These options include SQL_CACHE, SQL_NO_CACHE, SQL_SMALL_RESULT, SQL_BIG_RESULT, SQL_BUFFER_RESULT, SQL_CALC_FOUND_ROWS, and […]
Read More
MySQL can use an index for a WHERE, ORDER BY, or GROUP BY column; however, generally MySQL will select only one index per table. Starting with MySQL 5.0, there are a small number of exceptions when the optimizer may utilize more than one index, but in earlier versions this could result in a slower query. […]
Read More
While indexes can contain multiple columns, there is a practical limit in the effectiveness of the index. Indexes are part of the relational model to improve performance. The index row width should be as short as practical in order to provide as many index records per index data page. The benefit is to traverse the […]
Read More
It is possible for an index to have two or more columns. Multi column indexes are also known as compound or concatenated indexes. Let us look at a query that could use two different indexes on the table based on the WHERE clause restrictions. We first create these indexes. mysql> ALTER TABLE album -> ADD […]
Read More
When you have multiple different indexes that can be used for a query, MySQL tries to identify the most effective index for the query. It does so by analyzing statistics about the data distribution within each index. In our example, we are looking for all bands that were founded in 1980. Given these requirements, we […]
Read More
If you are unfamiliar with MySQL, or are familiar with other relational database systems, the concept of a storage engine can take some time to understand. In summary, although MySQL communicates and manages data via Structured Query Language (SQL), internally MySQL has different mechanisms to support the storage management and retrieval of the underlying data. […]
Read More
MySQL can use indexes to support a range of different functions. Indexes are not just for optimizing MySQL performance when reading data. These functions include the following: • Maintaining data integrity • Optimizing data access • Improving table joins • Sorting results • Aggregating data Data Integrity MySQL uses both primary and unique keys to […]
Read More
Identifying a slow running SQL query is a necessary prerequisite for any type of optimization. Here, we will detail some of the tools and principles required to determine the various options for an ideal solution. What You Should Not Do If you lived in the wild west, where no rules applied, you might consider adding […]
Read More
Users report that your application is too slow. After determining there is no physical system resource bottleneck, you turn your attention to the MySQL database. Finding a Slow SQL Statement Looking at the currently running MySQL connections with the SHOW FULL PROCESSLIST command, you find the following details: mysql> SHOW FULL PROCESSLIST\G … *************************** 6. […]
Read More
The role of information technology inside of corporations is not new; it has existed for decades. With each passing year, we see an acceleration in the speed and complexity of changes to our roles as IT professionals. Industries as a whole tend to resist change. This makes sense because industries are made of corporations, those […]
Read More