Optimizing Slow Select Queries: A Deep Dive

Introduction

As a web developer, you’ve probably encountered the frustration of slow database queries that can bring down your application’s performance. In this article, we’ll delve into the world of MySQL optimization and explore ways to improve the performance of a specific select query.

The Problem: 8-Second Select Query

Our friend is facing an issue with a select query that takes around 8 seconds to execute. The query retrieves recent chat messages from a database table, grouped by user who sent them. The query seems simple enough, but it’s causing significant delays.

Let’s take a closer look at the original query:

SELECT c.id, c.from, c.to, c.sent, c.message, c.recd 
FROM chat c 
WHERE c.id IN (
  SELECT MAX(id) FROM chat 
  WHERE (`to` = 1 and `del_to_status` = '0') or (`from` = 1 and `del_from_status` = '0')
  GROUP BY CASE WHEN 1 = `to` THEN `from` ELSE `to` END
)
ORDER BY id DESC
LIMIT 60;

Understanding the Query

This query is using a subquery to find the maximum id values for each group of chats. The groups are determined by a combination of conditions:

to = 1 and del_to_status = ‘0’
from = 1 and del_from_status = ‘0’

The subquery uses a GROUP BY clause to aggregate the results, but this can lead to slow performance if not optimized properly.

Assuming Indexed Chat Table

Our friend assumes that the chat table has an index on the id column. This is essential for optimizing the query, as indexes can significantly speed up queries that frequently filter or sort data.

However, even with an indexed chat table, MySQL might still struggle with subqueries. To improve performance, we need to explore alternative approaches.

Converting Subquery to Temporary Table

One possible solution is to convert the subquery into a temporary table and join it with the main query. This approach can provide better performance by reducing the number of rows being processed.

Let’s create a temporary table called max_chat_ids using the following query:

CREATE TEMPORARY TABLE IF NOT EXISTS max_chat_ids (
  INDEX (id)
) ENGINE=MEMORY AS (
  'SELECT MAX(id) as id FROM chat 
  WHERE (`to` = 1 and `del_to_status` = '0') or (`from` = 1 and `del_from_status` = '0')
  GROUP BY CASE WHEN 1 = `to` THEN `from` ELSE `to` END'
);

This query creates a temporary table with an index on the id column, which will help speed up the subsequent join.

Joining Temporary Table with Main Query

Now that we have our temporary table, we can join it with the main query:

SELECT c.id, c.from, c.to, c.sent, c.message, c.recd 
FROM chat c 
JOIN max_chat_ids d ON c.id = d.id
ORDER BY c.id DESC
LIMIT 60;

This query joins the max_chat_ids table with the main query on the id column. The resulting rows are then sorted in descending order and limited to 60.

Temporary Tables: What’s Happening Here?

Temporary tables are stored in memory, which means they’re faster than regular tables. This is because temporary tables don’t require disk I/O or disk swapping.

When we execute a query on a temporary table, MySQL can use the following steps:

Read the data from the temporary table into memory.
Filter and sort the data using the join condition.
Return the results to the client.

Temporary tables are only available during the duration of the session, which means they’re automatically dropped when the session is closed.

Testing and Execution

To test this optimized query, our friend should execute both queries together with a semicolon (;) between them:

CREATE TEMPORARY TABLE IF NOT EXISTS max_chat_ids (
  INDEX (id)
) ENGINE=MEMORY AS (
  'SELECT MAX(id) as id FROM chat 
  WHERE (`to` = 1 and `del_to_status` = '0') or (`from` = 1 and `del_from_status` = '0')
  GROUP BY CASE WHEN 1 = `to` THEN `from` ELSE `to` END'
);

SELECT c.id, c.from, c.to, c.sent, c.message, c.recd 
FROM chat c 
JOIN max_chat_ids d ON c.id = d.id
ORDER BY c.id DESC
LIMIT 60;

This ensures that both queries are executed and the temporary table is properly cleaned up.

Conclusion

By converting the subquery into a temporary table and joining it with the main query, we’ve significantly improved the performance of our friend’s select query. This approach can be applied to similar queries where subqueries or complex filtering conditions are used.

Remember to always consider indexing, join order, and other optimization techniques when improving query performance in MySQL.

Last modified on 2023-11-01