MySQL+SQLFlash

Take the optimization of a slow SQL in MySQL as an example.

In a business system of an educational platform, we found a slow SQL that takes 63 seconds to execute, causing delays in information display. Let’s see how SQLFlash optimizes it.

1. SQLFlash Optimized SQL Statement

What optimizations did SQLFlash perform?

Implicit Conversion Elimination: Convert the integer 111 to the string ‘111’ in the subquery’s filter condition ’e.namespace = 111’ to eliminate type inconsistency on the field, ensuring indexes can be effectively utilized.
COUNT Scalar Subquery Optimization: Replace the COUNT(*) in the subquery with an IN subquery using s.student_id IN (SELECT e.student_id FROM enrollments e WHERE e.student_id = s.student_id AND e.namespace = ‘111’) to utilize SEMI JOIN features and improve query efficiency.

Index Advise

1
2
CREATE INDEX idx_enrollments_student_id_namespace ON enrollments (student_id, namespace);
CREATE INDEX idx_students_major_dob ON students (major, date_of_birth);

To improve query performance, SQLFlash provided index recommendations based on the following insights:

Improving Join Efficiency: The query frequently filters rows in the enrollments table using conditions on student_id and namespace. An index covering these columns allows the database to retrieve relevant rows efficiently, avoiding full table scans.
Optimizing WHERE Clause Filtering: The students table applies multiple filtering conditions, including major and date_of_birth. Indexing these columns significantly reduces the number of rows scanned, especially for selective queries, and improves the efficiency of the LIMIT clause.

2. Performance Improvement Comparison

Query Execution Time:

Before Optimization: 1 min 3.76 sec
After Optimization: 0.12 sec

Run origin SQL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
mysql> SELECT s.student_name,
    ->        s.major,
    ->        s.gender,
    ->        s.date_of_birth
    -> FROM students s
    -> WHERE (SELECT COUNT(*)
    ->        FROM enrollments e
    ->        WHERE e.student_id = s.student_id
    ->          AND e.namespace = 111) > 0
    ->   and  s.major = 'Mathematics'
    ->   AND s.date_of_birth <= '2000-01-01'
    ->   AND s.student_name != CONCAT('Kevin',' ','Jenkins')
    -> limit 1;
+--------------+-------------+--------+---------------+
| student_name | major       | gender | date_of_birth |
+--------------+-------------+--------+---------------+
| Scott Wang   | Mathematics | Other  | 1994-08-08    |
+--------------+-------------+--------+---------------+
1 row in set, 19031 warnings (1 min 3.76 sec)

Add index And run Optimized SQL

1
2
3
4
mysql> CREATE INDEX idx_enrollments_student_id_namespace ON enrollments(student_id, namespace);
CREATE INDEX idx_students_major_dob ON students(major, date_of_birth);
Query OK, 0 rows affected (40.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

mysql> CREATE INDEX idx_students_major_dob ON students(major, date_of_birth);
Query OK, 0 rows affected (33.90 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> SELECT s.student_name,
    ->        s.major,
    ->        s.gender,
    ->        s.date_of_birth
    -> FROM students s
    -> WHERE s.student_id IN (
    ->     SELECT e.student_id
    ->     FROM enrollments e
    ->     WHERE e.student_id = s.student_id
    ->       AND e.namespace = '111'
    -> )
    ->   AND s.major = 'Mathematics'
    ->   AND s.date_of_birth <= '2000-01-01'
    ->   AND s.student_name != CONCAT('Kevin',' ','Jenkins')
    -> LIMIT 1;
+--------------+-------------+--------+---------------+
| student_name | major       | gender | date_of_birth |
+--------------+-------------+--------+---------------+
| Scott Wang   | Mathematics | Other  | 1994-08-08    |
+--------------+-------------+--------+---------------+
1 row in set (0.12 sec)