Optimize Slow Queries with RDS Performance Insights & EverSQL

In today's tech companies, engineers are expected to know and care more about the database and the performance of their application. To effectively face these challenging requirements, DBA, engineering and DevOps teams need the right tools to monitor, diagnose and resolve performance issues.

In this post, I'll demonstrate how one can track down slow SQL queries and optimize them automatically, by utilizing the recent integration of EverSQL Query Optimizer with Amazon RDS Performance Insights, using EverSQL's Chrome extension.

Monitoring and Optimizing Slow SQL Queries

As an AWS user, you probably already have Performance Instance enabled (as it's the default configuration). When navigating to the Performance Insights dashboard, the first thing you'll notice is the database load chart. Right below that, you'll see a list of SQL queries sorted by the amount of load each query is generating on your server. The load each query generates is measured and visualized using the Average Active Sessions (AAS) metric.

In this post, we'll use the StackOverflow database to demonstrate the optimization process. The SQL query at the top of the list seems to be very I/O intensive (as indicated by the light blue color). The query is scanning StackOverflow's comments table, looking to fetch the ones written at the first day of 2019, or the ones with very high scores (>500). The table contains roughly 6.5 million records.

SELECT
    *
FROM
    comments c
WHERE
    DATE(c.CreationDate) = '2019-01-01'
    OR c.score > 500
ORDER BY c.id DESC
LIMIT 1000;

The execution duration of the original query is 38 seconds.

If you have EverSQL's Chrome extension installed, you'll get an additional detailed report within the same page on RDS Performance Insights, explaining how each of the SQL queries can be optimized. EverSQL will automatically present the optimized query and suggest the optimal indexes you should create, to improve the query's performance.

After applying the recommendations provided by EverSQL, the execution duration decreased to roughly 20ms.

This is a short glance at how the integration looks like and how you can quickly send a slow query from AWS Performance Insights to optimization in EverSQL:

Optimization Internals

To better understand the internals of this optimization process, let's start from the beginning, by looking at the comments table's structure and the query's execution plan:

CREATE TABLE `comments` (
  `Id` int(11) NOT NULL,
  `CreationDate` datetime NOT NULL,
  `PostId` int(11) NOT NULL,
  `Score` int(11) DEFAULT NULL,
  `Text` text CHARACTER SET utf8 NOT NULL,
  `UserId` int(11) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  KEY `comments_idx_score` (`Score`),
  KEY `comments_idx_creationdate` (`CreationDate`),
  KEY `comments_idx_creationdate_score` (`CreationDate`,`Score`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

As you can see, MySQL didn't choose to use any of the indexes containing the Score or CreationDate columns. This is why:

1. The column CreationDate is hidden inside the DATE function, which prevents MySQL from using an index on that column. To overcome it, we can transform the original condition to one with a date range check (lines 24-25 in the query below).
2. There are two conditions in the WHERE clause, with an OR operator between them. In many similar cases, we saw that MySQL can't / won't use composite indexes effectively, nor use index_merge. Therefore, splitting the query to two parts combined with a UNION, will allow MySQL to use the relevant indexes.

So after applying the query transformations:

SELECT
  *
FROM
  (
    (
      SELECT
        *
      FROM
        comments c
      WHERE
        c.score > 500
      ORDER BY
        c.score DESC
      LIMIT
        1000
    )
    UNION
      DISTINCT (
        SELECT
          *
        FROM
          comments c
        WHERE
          c.CreationDate BETWEEN '2019-01-01 00:00:00'
          AND '2019-01-01 23:59:59'
        ORDER BY
          c.score DESC
        LIMIT
          1000
      )
  ) AS union1
ORDER BY
  union1.score DESC
LIMIT
  1000;

As you can see from the new execution plan, MySQL now uses the indexes for both columns.

As mentioned above, the optimized query's execution duration is roughly 20ms, which is significantly faster than the original one.

Wrapping up

If you're using AWS RDS and would like to optimize SQL queries directly from Performance Insights, all you have to do is install EverSQL's Chrome extension, to integrate EverSQL with RDS Performance Insights.

Feel free to send us your feedback at [email protected].