Query Optimization Techniques - MySQL

[akash-coded]

MySQL query optimization is a crucial task to ensure that your database operations run smoothly and efficiently.

Here's an in-depth explanation of the various strategies, techniques, best practices, and hidden techniques for MySQL query optimization, using examples from the classicmodels database for illustration.

1. Indexing: Indexing is an efficient way to speed up the performance of a database by reducing the number of disk accesses required when a query is executed. Here is an example of creating an index on the customerNumber column in the orders table:

   CREATE INDEX idx_order_customerNumber 
   ON orders (customerNumber);

   Note: Indexing comes with the trade-off of taking up more storage space, and slowing down write operations (INSERT, UPDATE, DELETE). So, indexes should be used judiciously, mainly on columns that are frequently used in the WHERE clause, JOIN conditions, or as sorting criteria.

2. Use EXPLAIN: The EXPLAIN keyword in MySQL is used to provide a detailed insight into how MySQL executes a query. It helps to understand the query execution plan and identify the bottlenecks. For example, suppose we have the following query:

   EXPLAIN SELECT * FROM orders WHERE orderNumber = 10100;

   The output of EXPLAIN can help identify potential optimizations.

3. *Avoid SELECT : Instead of using SELECT *, specify the columns that you want to retrieve to decrease the amount of data that needs to be read from the disk. For instance:

   SELECT orderNumber, orderDate FROM orders WHERE orderNumber = 10100;

4. Use LIMIT: If you only need a certain number of rows, use the LIMIT keyword to avoid reading extra rows. For instance:

   SELECT * FROM orders LIMIT 10;

5. Avoid Correlated Subqueries: A correlated subquery is a subquery that depends on the outer query. It can result in the subquery being executed once for every row processed by the outer query. This can slow down your query significantly. It's often possible to rewrite correlated subqueries using joins.

6. Use JOINs Instead of Subqueries: If you need to combine data from multiple tables, use JOINs instead of subqueries as they are generally faster.

7. Normalization and Denormalization: Normalization reduces data redundancy but can lead to complex queries. Denormalization reduces the complexity of queries but may lead to data redundancy. Use a balance of both based on the specific use case.

8. Data Types: Use the most efficient data type possible. For example, INT is more efficient than DECIMAL or CHAR.

9. Partitioning: MySQL supports table partitioning, which can be a great way to optimize large tables.

10. Caching: MySQL has a built-in query cache. If enabled, it stores the result set of a SELECT statement, so if an identical statement is executed, the server can then retrieve the results from the cache rather than executing it again.

In terms of the dos and don'ts:

• Do make use of indexes, but do not over-index.
• Do normalize data to eliminate redundancy.
• Do use the most efficient data types.
• Don't use SELECT * unless necessary.
• Don't use correlated subqueries if a JOIN would work.
• Don't rely on MySQL's query cache for complex queries or large databases.

11. Use Batch Inserts and Updates: If you need to insert or update a large number of rows at once, it's more efficient to perform these operations in batches rather than row by row.

12. Prefer UNION ALL over UNION: UNION performs an additional step of removing duplicate rows, so UNION ALL is faster if you know there are no duplicates, or duplicates are not a problem for your result set.

13. Avoid using functions in predicates: Using functions in the WHERE clause can prevent MySQL from effectively using indexes, slowing down the query. Instead of writing WHERE MONTH(date) = 5, it's more efficient to write WHERE date BETWEEN '2023-05-01' AND '2023-05-31'.

14. Avoid OR if you can use UNION: An OR operation cannot use an index while a UNION can. For example, instead of

    SELECT * FROM orders WHERE orderNumber = 10100 OR orderNumber = 10101;

    You can write:

    SELECT * FROM orders WHERE orderNumber = 10100
    UNION ALL
    SELECT * FROM orders WHERE orderNumber = 10101;

15. Use Wildcards at the end: When using LIKE operator, it's better to use the wildcard at the end of the phrase as it allows MySQL to look for exact matches from the start. Using wildcard at the beginning can lead to full table scans.

Best Practices:

1. Regularly update statistics: The database collects statistics about the distribution of the key values in each index and uses these statistics to determine the order in which tables should be joined when you perform a query that uses more than one table.

2. Optimize your database regularly: Optimization defragments your database so that it can run more efficiently.

3. Use persistent connections: Persistent connections are database connections that are kept open across multiple requests and are re-used, which saves the overhead of establishing a new connection every time a connection is requested.

4. Avoid large temporary tables: If MySQL is using large temporary tables while executing your query, it's a good indication that the query could be optimized.

Interview Questions:

Question-1: You notice that a SELECT query which includes several JOIN operations is performing poorly. What steps would you take to improve its performance?

Answer: The steps can be:

1. Use the EXPLAIN keyword to understand the execution plan of the query and identify any potential bottlenecks.
2. Ensure that the columns being joined on are indexed. This can significantly speed up JOIN operations.
3. Check if all JOINs are necessary. If possible, remove unnecessary JOINs.
4. Instead of joining all tables at once, consider breaking down the query into smaller parts using temporary tables.
5. If the data in the tables doesn't change frequently, consider using a denormalized table or a view that can be updated periodically.

Question-2: You are tasked with optimizing a MySQL database that supports a web application with high transaction loads. What are some strategies you would consider?

Answer:

1. Use appropriate indexing to speed up read queries.
2. Normalize tables to eliminate redundancy but also consider denormalization for frequently used complex queries.
3. Partition large tables to improve performance.
4. Batch inserts or updates to reduce the number of individual operations.
5. Regularly update statistics to keep the database's self-optimization up-to-date.
6. Avoid functions in predicates and try to use wildcards at the end in LIKE operations.
7. Regularly optimize the database to defragment the data.
8. Use persistent connections to save on connection overhead.
9. Use EXPLAIN to analyze queries and find bottlenecks.
10. Consider caching mechanisms for frequent queries with the same result.

More Examples:

Let's dive deeper into some of these techniques:

1. Indexing: Indexing is the process of adding indexes to a database table to improve the speed of data retrieval operations. Indexes can be created on any combination of columns in a MySQL database table and can significantly increase query performance. However, indexes come at the cost of additional storage and slower write operations.

   Here's an example where indexing can make a substantial difference:

   -- Create an index on the customerNumber column in the payments table
   CREATE INDEX idx_payments_customerNumber 
   ON payments (customerNumber);
   
   -- Before creating the index, this query might have to perform a full table scan
   SELECT * FROM payments WHERE customerNumber = 103;

2. Use EXPLAIN: The EXPLAIN statement in MySQL is used to provide information about how MySQL executes a SELECT statement. EXPLAIN provides information about the tables accessed by the query and the order in which they are accessed.

   For instance, consider this query:

   SELECT o.orderNumber, p.amount 
   FROM orders o JOIN payments p USING(customerNumber) 
   WHERE p.checkNumber = 'HQ336336';
   
   -- Using EXPLAIN
   EXPLAIN SELECT o.orderNumber, p.amount 
   FROM orders o JOIN payments p USING(customerNumber) 
   WHERE p.checkNumber = 'HQ336336';

   By using EXPLAIN, you get insight into how MySQL plans to execute the query, such as which indexes it plans to use, how it's going to scan the tables, and the estimated number of rows to be read. It helps you identify parts of your query that might need optimization.

3. *Avoid SELECT : Using SELECT * retrieves all columns from the table. If your table has a large number of columns or large amounts of data, this can cause unnecessary load on the database server and network.

   Instead, specify only the columns you need. For example:

   -- Instead of this
   SELECT * FROM customers;
   
   -- Use this
   SELECT customerNumber, customerName FROM customers;

4. Use LIMIT: If you are only interested in a subset of rows, such as the top 10 or a page of results, use the LIMIT clause to restrict the number of rows returned by the query. This avoids the cost of sending and processing unnecessary rows.

   For instance:

   -- Instead of this
   SELECT productName FROM products ORDER BY buyPrice DESC;
   
   -- Use this
   SELECT productName FROM products ORDER BY buyPrice DESC LIMIT 10;

5. Avoid Correlated Subqueries: Correlated subqueries can be a significant performance problem because for each row processed by the outer query, the subquery is executed anew.

   For example, consider the query that retrieves the customers who have payments above the average payment of their respective sales reps:

   SELECT c.customerNumber, c.customerName
   FROM customers c
   WHERE (
       SELECT AVG(amount) 
       FROM payments p 
       WHERE p.salesRepEmployeeNumber = c.salesRepEmployeeNumber
   ) < (
       SELECT AVG(amount) 
       FROM payments 
       WHERE customerNumber = c.customerNumber
   );

   This could be significantly optimized by using a JOIN operation to avoid the repeated execution of the subquery.

6. Batch Inserts and Updates: Performing insert or update operations one at a time can be time-consuming, especially if you're working with large datasets. Instead, it's often more efficient to perform these operations in batches. Here is an example of a batch insert:

   INSERT INTO payments(customerNumber, checkNumber, paymentDate, amount) 
   VALUES 
   (103, 'HQ33653', '2004-10-19', 6066.78),
   (103, 'JM555205', '2005-05-03', 1676.14),
   (103, 'OM314933', '2004-12-18', 14191.12);

7. Prefer UNION ALL over UNION: The UNION operator combines the result sets of two or more SELECT statements, then eliminates duplicate rows to return a single result set. However, this extra step of removing duplicates can add significant overhead. If you know there won't be duplicates, or if duplicates aren't an issue for your result set, use UNION ALL instead.

   For example, let's say you want to list all customers who have either a credit limit over $100,000 or who are located in San Francisco. You could use a UNION like so:

   SELECT customerNumber, customerName FROM customers WHERE creditLimit > 100000
   UNION
   SELECT customerNumber, customerName FROM customers WHERE city = 'San Francisco';

   If you don't need to remove duplicates, you could use UNION ALL to improve performance:

   SELECT customerNumber, customerName FROM customers WHERE creditLimit > 100000
   UNION ALL
   SELECT customerNumber, customerName FROM customers WHERE city = 'San Francisco';

8. Avoid using functions in predicates: Using functions in the WHERE clause can prevent MySQL from effectively using indexes, resulting in slower query performance. Here's an example:

   Let's say you want to find all payments made in the month of May, 2004. Instead of writing:

   SELECT * FROM payments WHERE MONTH(paymentDate) = 5 AND YEAR(paymentDate) = 2004;

   It would be more efficient to use a range condition like this:

   SELECT * FROM payments WHERE paymentDate BETWEEN '2004-05-01' AND '2004-05-31';

   The second query can make better use of an index on the paymentDate column if one exists.

9. Avoid OR if you can use UNION: An OR operation cannot use an index, whereas a UNION can. Instead of:

   SELECT * FROM orders WHERE orderNumber = 10100 OR orderNumber = 10101;

   You can write:

   SELECT * FROM orders WHERE orderNumber = 10100
   UNION ALL
   SELECT * FROM orders WHERE orderNumber = 10101;

10. Use Wildcards at the end: When using the LIKE operator, placing the wildcard at the end of the phrase allows MySQL to look for exact matches from the start. Using a wildcard at the beginning can lead to full table scans.

    For example, instead of:

    SELECT * FROM products WHERE productName LIKE '%scale%';

    Use:

    SELECT * FROM products WHERE productName LIKE '1:18 scale%';

Remember, while these are general strategies, not every technique will be applicable or beneficial in every situation. It's crucial to understand the specific requirements and constraints of your database, data, and queries when deciding on the best optimization techniques.

Remember, query optimization is part science and part art, and requires a solid understanding of both the data and the underlying database structure.