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
MySQL® is one of the most popular relational database management systems being used today. Its popularity with SQL admins who manage data sets puts it at the back end of many tiered applications. But with great popularity comes great responsibility, especially for an IT admin who is new to monitoring and managing MySQL instances. This is a different […]
Read More
One of the best things about being a DBA is that I am always learning. As a long time DBA (+25 years), who has worked with about every database type imaginable, I find that my latest challenge is to keep up with all the new features and functionality the latest versions have to offer. This […]
Read More
Provisioning a new server has become increasingly easy with virtualization. Provisioning data, however, can be more challenging, especially in Agile shops where the volume of environments required to support dev and testing can easily go beyond the abilities of your IT operations to keep up. Is there any way to make it easier? We brought […]
Read More
Lately, the big IT buzzword is cloud solutions. In fact, if you believe all of the hype, most companies are moving en masse from on-premise solutions to cloud-based architectures. On the surface, the cloud-based solution certainly seems to be a more affordable solution that not only reduces the complexities of on-premise build-outs and maintenance, but […]
Read More