Optimize Database Queries for Improved Performance

Introduction

In this project, you will learn how to optimize database queries to improve performance in the LabEx system. The LabEx system is a platform that manages user study records, courses, and other related data. The project focuses on optimizing three frequently used queries in the system.

👀 Preview

🎯 Tasks

In this project, you will learn:

How to add a compound index on the user and study_time fields of the usercourse table to improve query performance.
How to optimize the first query to find the study time of a specific user for a specific course.
How to optimize the second query to sort the study records of a user's courses by study time.
How to optimize the third query to retrieve all the courses that a user has studied.

🏆 Achievements

After completing this project, you will be able to:

Understand the importance of indexing in database optimization.
Implement compound indexes to improve the performance of complex queries.
Analyze query execution plans to identify performance bottlenecks.
Optimize database queries by leveraging appropriate indexes.
Apply your knowledge to improve the performance of real-world database applications.

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL mysql(("`MySQL`")) -.-> mysql/BasicKeywordsandStatementsGroup(["`Basic Keywords and Statements`"]) sql(("`SQL`")) -.-> sql/DataManipulationandQueryingGroup(["`Data Manipulation and Querying`"]) mysql(("`MySQL`")) -.-> mysql/AdvancedQueryingandOptimizationGroup(["`Advanced Querying and Optimization`"]) sql(("`SQL`")) -.-> sql/BasicSQLCommandsGroup(["`Basic SQL Commands`"]) sql(("`SQL`")) -.-> sql/DatabaseManagementandOptimizationGroup(["`Database Management and Optimization`"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/source("`External Code Execution`") sql/DataManipulationandQueryingGroup -.-> sql/where("`WHERE clause`") sql/DataManipulationandQueryingGroup -.-> sql/order_by("`ORDER BY clause`") mysql/BasicKeywordsandStatementsGroup -.-> mysql/select("`Data Retrieval`") mysql/BasicKeywordsandStatementsGroup -.-> mysql/alter_table("`Table Modification`") mysql/BasicKeywordsandStatementsGroup -.-> mysql/use_database("`Database Selection`") mysql/AdvancedQueryingandOptimizationGroup -.-> mysql/index("`Index Management`") sql/BasicSQLCommandsGroup -.-> sql/alter_table("`ALTER TABLE statements`") sql/DatabaseManagementandOptimizationGroup -.-> sql/creating_indexes("`Creating Indexes`") subgraph Lab Skills mysql/source -.-> lab-301386{{"`Query Is So Slow`"}} sql/where -.-> lab-301386{{"`Query Is So Slow`"}} sql/order_by -.-> lab-301386{{"`Query Is So Slow`"}} mysql/select -.-> lab-301386{{"`Query Is So Slow`"}} mysql/alter_table -.-> lab-301386{{"`Query Is So Slow`"}} mysql/use_database -.-> lab-301386{{"`Query Is So Slow`"}} mysql/index -.-> lab-301386{{"`Query Is So Slow`"}} sql/alter_table -.-> lab-301386{{"`Query Is So Slow`"}} sql/creating_indexes -.-> lab-301386{{"`Query Is So Slow`"}} end

Add a Compound Index

In this step, you will learn how to add a compound index on the user and study_time fields of the usercourse table.

Start the MySQL service.
```
sudo /etc/init.d/mysql start
```
Log into the MySQL terminal.
```
mysql -uroot
```
Import the data from the provided initDatabase.sql script into the MySQL database:

SOURCE /home/labex/project/initDatabase.sql

Create the addIndex.sql file in the /home/labex/project directory.
In the addIndex.sql file, add the following SQL statement to create the compound index:
```
ALTER TABLE labex.usercourse ADD INDEX ix_user_study_time (user, study_time);
```
This statement creates a compound index named ix_user_study_time on the user and study_time fields of the usercourse table.
Save the addIndex.sql file.
In the MySQL terminal, run the following command to execute the SQL script:
```
SOURCE /home/labex/project/addIndex.sql
```
This will create the compound index in the database.

Verify the index creation by running the following SQL statement:

SHOW INDEX FROM usercourse;

The output should show the new compound index.

MariaDB [labex]> SHOW INDEX FROM usercourse;
+------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table      | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| usercourse |          0 | PRIMARY            |            1 | ID          | A         |          10 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| usercourse |          1 | ix_user_study_time |            1 | user        | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| usercourse |          1 | ix_user_study_time |            2 | study_time  | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
+------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
3 rows in set (0.001 sec)

Optimize Query 1 – Find the Study Time of User A for Course B

In this step, you will learn how to optimize the first query to find the study time of user A for course B.

Open the MySQL terminal.
Explain the original query:
```
EXPLAIN SELECT study_time
FROM usercourse
WHERE user = 'A' AND course = 'B';
```
This will show the execution plan for the original query, which you can use to identify potential performance bottlenecks.
Modify the query to use the compound index created in the previous step:
```
SELECT study_time
FROM usercourse
WHERE user = 'A' AND course = 'B';
```
The compound index on user and study_time will help the database efficiently locate the relevant row(s) and retrieve the study_time value.

Explain the modified query:

EXPLAIN SELECT study_time
FROM usercourse
WHERE user = 'A' AND course = 'B';

The output should show that the database is using the ix_user_study_time index to execute the query, which should result in better performance.

MariaDB [labex]> EXPLAIN SELECT study_time
 -> FROM usercourse
 -> WHERE user = 'A' AND course = 'B';
+------+-------------+------------+------+--------------------+------+---------+------+------+-------------+
| id   | select_type | table      | type | possible_keys      | key  | key_len | ref  | rows | Extra       |
+------+-------------+------------+------+--------------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | usercourse | ALL  | ix_user_study_time | NULL | NULL    | NULL | 10   | Using where |
+------+-------------+------------+------+--------------------+------+---------+------+------+-------------+
1 row in set (0.000 sec)

MariaDB [labex]> SELECT study_time
    -> FROM usercourse
    -> WHERE user = 'A' AND course = 'B';
+------------+
| study_time |
+------------+
| 50         |
| 130        |
| 60         |
| 70         |
| 80         |
| 90         |
| 100        |
| 110        |
| 120        |
| 50         |
+------------+
10 rows in set (0.000 sec)

MariaDB [labex]> EXPLAIN SELECT study_time
    -> FROM usercourse
    -> WHERE user = 'A' AND course = 'B';
+------+-------------+------------+------+--------------------+------+---------+------+------+-------------+
| id   | select_type | table      | type | possible_keys      | key  | key_len | ref  | rows | Extra       |
+------+-------------+------------+------+--------------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | usercourse | ALL  | ix_user_study_time | NULL | NULL    | NULL | 10   | Using where |
+------+-------------+------------+------+--------------------+------+---------+------+------+-------------+
1 row in set (0.000 sec)

Optimize Query 2 – Sort the Study Records of User A’s Courses by Study Time

In this step, you will learn how to optimize the second query to sort the study records of user A's courses by study time.

Open the MySQL terminal.
Explain the original query:
```
EXPLAIN SELECT *
FROM usercourse
WHERE user = 'A'
ORDER BY study_time;
```
This will show the execution plan for the original query, which you can use to identify potential performance bottlenecks.
Modify the query to use the compound index created in the first step:
```
SELECT *
FROM usercourse
WHERE user = 'A'
ORDER BY study_time;
```
The compound index on user and study_time will help the database efficiently locate the relevant rows and sort them by study_time.

Explain the modified query:

EXPLAIN SELECT *
FROM usercourse
WHERE user = 'A'
ORDER BY study_time;

The output should show that the database is using the ix_user_study_time index to execute the query, which should result in better performance.

MariaDB [labex]> EXPLAIN SELECT *
 -> FROM usercourse
 -> WHERE user = 'A'
 -> ORDER BY study_time;
+------+-------------+------------+------+--------------------+------+---------+------+------+-----------------------------+
| id   | select_type | table      | type | possible_keys      | key  | key_len | ref  | rows | Extra                       |
+------+-------------+------------+------+--------------------+------+---------+------+------+-----------------------------+
|    1 | SIMPLE      | usercourse | ALL  | ix_user_study_time | NULL | NULL    | NULL | 10   | Using where; Using filesort |
+------+-------------+------------+------+--------------------+------+---------+------+------+-----------------------------+
1 row in set (0.000 sec)

MariaDB [labex]> SELECT *
    -> FROM usercourse
    -> WHERE user = 'A'
    -> ORDER BY study_time;
+----+------+--------+------------+
| ID | user | course | study_time |
+----+------+--------+------------+
| 6  | A    | B      | 100        |
| 7  | A    | B      | 110        |
| 8  | A    | B      | 120        |
| 10 | A    | B      | 130        |
| 1  | A    | B      | 50         |
| 9  | A    | B      | 50         |
| 2  | A    | B      | 60         |
| 3  | A    | B      | 70         |
| 4  | A    | B      | 80         |
| 5  | A    | B      | 90         |
+----+------+--------+------------+
10 rows in set (0.000 sec)

MariaDB [labex]> EXPLAIN SELECT *
    -> FROM usercourse
    -> WHERE user = 'A'
    -> ORDER BY study_time;
+------+-------------+------------+------+--------------------+------+---------+------+------+-----------------------------+
| id   | select_type | table      | type | possible_keys      | key  | key_len | ref  | rows | Extra                       |
+------+-------------+------------+------+--------------------+------+---------+------+------+-----------------------------+
|    1 | SIMPLE      | usercourse | ALL  | ix_user_study_time | NULL | NULL    | NULL | 10   | Using where; Using filesort |
+------+-------------+------------+------+--------------------+------+---------+------+------+-----------------------------+
1 row in set (0.000 sec)

Optimize Query 3 – Query All the Courses That User a Has Studied

In this step, you will learn how to optimize the third query to retrieve all the courses that user A has studied.

Open the MySQL terminal.
Explain the original query:
```
EXPLAIN SELECT course
FROM usercourse
WHERE user = 'A';
```
This will show the execution plan for the original query, which you can use to identify potential performance bottlenecks.
Modify the query to use the compound index created in the first step:
```
SELECT DISTINCT course
FROM usercourse
WHERE user = 'A';
```
The compound index on user and study_time will help the database efficiently locate the relevant rows and retrieve the distinct course values.

Explain the modified query:

EXPLAIN SELECT DISTINCT course
FROM usercourse
WHERE user = 'A';

The output should show that the database is using the ix_user_study_time index to execute the query, which should result in better performance.

MariaDB [labex]> EXPLAIN SELECT course
 -> FROM usercourse
 -> WHERE user = 'A';
+------+-------------+------------+------+--------------------+------+---------+------+------+-------------+
| id   | select_type | table      | type | possible_keys      | key  | key_len | ref  | rows | Extra       |
+------+-------------+------------+------+--------------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | usercourse | ALL  | ix_user_study_time | NULL | NULL    | NULL | 10   | Using where |
+------+-------------+------------+------+--------------------+------+---------+------+------+-------------+
1 row in set (0.000 sec)

MariaDB [labex]> SELECT DISTINCT course
    -> FROM usercourse
    -> WHERE user = 'A';
+--------+
| course |
+--------+
| B      |
+--------+
1 row in set (0.000 sec)

MariaDB [labex]> EXPLAIN SELECT DISTINCT course
    -> FROM usercourse
    -> WHERE user = 'A';
+------+-------------+------------+------+--------------------+------+---------+------+------+------------------------------+
| id   | select_type | table      | type | possible_keys      | key  | key_len | ref  | rows | Extra                        |
+------+-------------+------------+------+--------------------+------+---------+------+------+------------------------------+
|    1 | SIMPLE      | usercourse | ALL  | ix_user_study_time | NULL | NULL    | NULL | 10   | Using where; Using temporary |
+------+-------------+------------+------+--------------------+------+---------+------+------+------------------------------+
1 row in set (0.000 sec)

By following these steps, you have learned how to add a compound index and optimize the three queries in the LabEx system to improve their performance.