SQL QUERY ANALYSER

SQL query analayser analyses the given python file by the user and do the following.

1. List all the queries.

2. Checking if the table exists.

3. Checking if the column exists.

4. Execute an 'explain' on queries and listing indexes for each query

5. Smart Suggestions

   For each table - suggesting new indexes and also telling what indexes aren't useful. [Reducing time complexity]

6. Send the report to the given email_id.

Prerequisites

1. MySQL
2. Python3
3. PyMySQL

How to run

1. Write the MySQL credentials in config.ini file.
2. Insert the file which you want to analyse in the search_file directory.
3. Run drive.py and enter the filename for which you want to generate the report. Also enter the email-Id to send the report.

How it works

1. remove_comments.py

   Removes comments from the input file.

2. query_detector.py

   Detects SQL queries in the input file and stores those queries in another file (i.e.output.txt)

3. extract_performance_query.py

   From output.txt, selects only those queries for which indexing is possible (SQL statements containing 'where') and stores them in a new file (updated_queries.txt)

4. check_table_and_column.py

   This file contains functions which execute the different SQL queries. Generally this is the utility file which is used in table_classifcation.py

5. table_classification.py

   This is the where the magic happens!

   This file contains function which replaces the placeholders in the SQL queries. Also functions which find the old_index and suggests new indexes. Storing the result in analyser.csv

6. python_mysql_dbconfig.py

   This script file reads the config.ini which contains the information required for the connection to the MySQL server.

7. mail.py

   This file sends the mail to the mail_id provided by the user. The mail contains the report i.e. analyser.csv.

8. drive.py

   This is the main file or driver file which calls all the above functions.

analyser.csv This is the final report generated by the script.

The file contains the following columns:

1. Table Name: Contains the name of the table found in the SQL queries.
2. Table Found: Contains the boolean (True or False) if the table is found in SQL Database.
3. Columns Not Found: Contains the list of columns which are not found in the given table for any SQL queries.
4. Queries: Contains the queries which does not contain the columns that are not found in the table name.
5. New Index Suggest: Contains the list of new index suggested by the script.
6. Index Not Helpul: Contains the list of indexes that are present in the given table but are not helpful.