Page 117 - Computer Software Application TP - Volume 1
P. 117

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
   112   113   114   115   116   117   118   119   120   121   122