MySQL Query Analyzer

MySQL Select Query Analyzer

Below python script will analyzes an SQL query before executing it. 

It provides details such as the number of tables, joins, subqueries, 

and unions in the query. Additionally, it retrieves details about 

the tables involved in the query, including the table name, index name,

column name, non-uniqueness, number of rows, data size, and index size. 

The script also displays the MySQL explain plan for the given query. 

 

import sqlparse
import pymysql.cursors

# Default Test SQL query

# sql_query = """
# SELECT 
#     t1.id, t1.name, t2.age
# FROM 
#     table1 AS t1
# JOIN 
#     table2 AS t2 ON t1.id = t2.id
# WHERE 
#     t1.name = 'John';
# """
# Override default Test SQL query
sql_query = input("Enter your SQL query: ")

# Parse the SQL query
parsed = sqlparse.parse(sql_query)[0]

# Extract table names
tables = set()
for token in parsed.tokens:
    if isinstance(token, sqlparse.sql.IdentifierList):
        for identifier in token.get_identifiers():
            tables.add(identifier.get_real_name())
    elif isinstance(token, sqlparse.sql.Identifier):
        tables.add(token.get_real_name())

# Initialize counts
join_count = 0
union_count = 0
subquery_count = 0

# Count joins, unions, and subqueries
for token in parsed.tokens:
    if isinstance(token, sqlparse.sql.Where):
        subquery_count += token.value.upper().count('SELECT') - 1
    elif token.value.upper() == 'JOIN':
        join_count += 1
    elif token.value.upper() == 'UNION':
        union_count += 1

print("Number of Tables:", len(tables))
print("Number of Joins:", join_count)
print("Number of Unions:", union_count)
print("Number of Subqueries:", subquery_count)

# Connect to the database
connection = pymysql.connect(
    host='your_host',
    user='your_username',
    password='your_password',
    database='your_database',
    cursorclass=pymysql.cursors.DictCursor
)

try:
    with connection.cursor() as cursor:
        for table in tables:
            # Get table indexes
            cursor.execute(f"SHOW INDEX FROM {table};")
            indexes = cursor.fetchall()
            for index in indexes:
                print("Index Name:", index['Key_name'])
                print("Column Name:", index['Column_name'])
                print("Non-unique:", index['Non_unique'])
                print()

        cursor.execute("SELECT table_name, table_rows, data_length, index_length FROM information_schema.TABLES WHERE table_schema = 'your_database';")
        tables = cursor.fetchall()

        for table in tables:
            table_name = table['table_name']
            table_rows = table['table_rows']
            data_length = table['data_length']
            index_length = table['index_length']

            # Convert bytes to MB for easier reading
            data_length_mb = data_length / (1024 * 1024)
            index_length_mb = index_length / (1024 * 1024)

            print(f"Table: {table_name}")
            print(f"Rows: {table_rows}")
            print(f"Data Size: {data_length_mb:.2f} MB")
            print(f"Index Size: {index_length_mb:.2f} MB")
            print()

        # Explain query plan
        cursor.execute(f"EXPLAIN EXTENDED {sql_query};")
        result = cursor.fetchall()
        for row in result:
            print(row)
finally:
    connection.close()