Page 117 - Computer Software Application TP - Volume 1

COMPUTER SOFTWARE APPLICATION - CITS

In this output, you can see information about the index, including its name (idx example column), the indexed
column (example column), and the index type (BTREE).
Test the query performance
After creating the index, you can test the performance of queries involving the indexed column to see if there is
an improvement in retrieval speed.

TASK 2 : QUERY OPTIMIZATION
Optimizing MySQL queries is crucial for improving database performance. Here are steps you can take to optimize
queries in MySQL on a Windows environment:

1 Identify slow queries-
Open MySQL Command Line or a MySQL client.
Execute the following command to enable the slow query log in your MySQL configuration file (my.ini):
SET GLOBAL slow query log = ‘ON’;
SET GLOBAL slow query log file = ‘C:\\path\\to\\your\\log\\file.log’;

SET GLOBAL long query time = 1;
Replace ‘C:\path\to\your\log\file.log’ with the desired path for your slow query log file. The long query time is set
to 1 second in this example, meaning queries taking longer than 1 second will be logged.

2 Analyze the slow query log:
Execute slow queries in your application or environment.
After some time, check the slow query log for potential issues:
SHOW VARIABLES LIKE ‘slow query log’;
SHOW VARIABLES LIKE ‘slow query log file’;

3 Use EXPLAIN to analyze queries-
Before optimizing, use the EXPLAIN statement to analyze the execution plan of a slow query:
EXPLAIN SELECT * FROM your table WHERE your condition;
Review the output to understand how MySQL is executing the query and identify potential bottlenecks.
4 Add Indexes:

Identify columns used in WHERE, JOIN, or ORDER BY clauses.
Create indexes on these columns to speed up query execution:
CREATE INDEX idx your column ON your table(your column);
Replace idx your column, your table, and your column with appropriate names.
5 Optimize WHERE Clause:

Use appropriate indexing to optimize WHERE clauses.
Avoid using functions or operations in the WHERE clause that prevent the use of indexes.

102
CITS : IT & ITES - Computer Software Application - Exercise 30