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
Running multiple SQL Server and Oracle database platforms to support different applications is the norm for many IT organizations today. While in many cases the two different database platforms often operate as independent islands there are also times you might need to integrate the two database platforms to perform lookups, queries and other operations that […]
Read More
Maintenance plans are one of the hidden jewels in the SQL Server tool chest that can really be a godsend to the beginning DBA or accidental DBA that is tasked to manage SQL Server. Maintenance Plans enable you to quickly and easily create scheduled backups, database integrity checks, index reorganizations and more – most of […]
Read More
Like most Microsoft products, sometimes the licensing can be more difficult to figure out than the technical aspects of the product, and SQL Server is no different. Each SQL Server edition has a different licensing model and some have more than one. There are multiple different license types. And if that’s not enough there are […]
Read More