SQL Statement Performance Anlaysis Methods

Execution Frequency Inspection

View execution counts for different SQL statement types:

SHOW GLOBAL STATUS LIKE 'COM_______';

Slow Query Log Configuration

Records queries exceeding a specified duration. Enable via MySQL configuration file (e.g., .cnf):

slow_query_log = 1
long_query_time = 2  # Log queries taking >2 seconds

Log location on Linux: /var/lib/mysql/localhost-slow.log.

Profiling Variables

Analyze per-query executino time breakdown:

-- Check profiling support
SELECT @@have_profiling;

-- Enable profiling
SET profiling = 1;

-- Execute target queries...

-- List recent queries with IDs and durations
show profiles;

-- Inspect CPU usage for a specific query (replace query_id)
show profile cpu for query 123;

Explain Output

Reveal query execution plan, including join order and index usage. Prefix any SELECT with EXPLAIN:

EXPLAIN SELECT * FROM target_table WHERE condition;

Regular Index Usage

Indexes accelerate filtering when columns match query conditions. For example, querying username in employee_records:

-- With index on username: uses index scan
SELECT * FROM employee_records WHERE username = 'jane_doe';

-- Without index: full table scan (slower)
SELECT * FROM employee_records WHERE username = 'john_smith';

For AND conditions: if columns have individual indexes, MySQL uses one. With a composite index on both columns, the composite index is used. Non-clustered index queries may trigger bookmark lookups (re-querying the clustered index using primary keys from the non-clustered index).

Composite Index Usage

Leftmost Prefix Principle

Composite indexes require matching column order from left to right. If a column is omitted, all columns to its right in the index become invalid. For an index on (A, B, C), valid subsets are (A), (A,B), and (A,B,C).

Example with index on (job_title, user_age, account_status) in user_data:

-- Uses full composite index (all 3 columns)
EXPLAIN SELECT * FROM user_data 
WHERE job_title = 'Engineer' AND user_age = 30 AND account_status = 'active';

-- Uses first 2 columns of index
EXPLAIN SELECT * FROM user_data 
WHERE job_title = 'Designer' AND user_age = 28;

-- Uses first column only
EXPLAIN SELECT * FROM user_data WHERE job_title = 'Manager';
EXPLAIN SELECT * FROM user_data 
WHERE job_title = 'Analyst' AND account_status = 'pending';

-- Index unused (omits leftmost column)
EXPLAIN SELECT * FROM user_data WHERE user_age = 35;

-- Order-independent if all columns present (uses full index)
EXPLAIN SELECT * FROM user_data 
WHERE user_age = 32 AND job_title = 'Developer' AND account_status = 'active';

Range Queries

In composite indexes, range conditions (>, <) invalidate indexes for columns to their right. Inclusive ranges (>=, <=) preserve right-column index usage.

Example with same index:

-- user_age > 25: index on job_title and user_age used; account_status index invalid
EXPLAIN SELECT * FROM user_data 
WHERE job_title = 'Engineer' AND user_age > 25 AND account_status = 'active';

-- user_age >= 25: all 3 columns' indexes remain active
EXPLAIN SELECT * FROM user_data 
WHERE job_title = 'Engineer' AND user_age >= 25 AND account_status = 'active';

Covering Indexes

Queries retrieve data entirely from the index, avoiding bookmark lookups. Avoid SELECT *; specify indexed columns.

Example with composite index on (job_title, user_age, account_status):

-- Covering index: returns id (primary key) and job_title (in index)
EXPLAIN SELECT id, job_title FROM user_data 
WHERE job_title = 'Engineer' AND user_age = 30 AND account_status = 'active';

-- Non-covering: requires bookmark lookup (full row retrieval)
EXPLAIN SELECT * FROM user_data 
WHERE job_title = 'Engineer' AND user_age = 30 AND account_status = 'active';

Prefix Indexes

For long string columns, create indexes on the first n characters to reduce size:

-- Create prefix index on email (first 15 chars)
CREATE INDEX idx_email_prefix ON employee_records(email(15));

Forcing Index Selection

MySQL chooses indexes automatically, but you can override this:

-- Suggest index (MySQL may ignore)
EXPLAIN SELECT * FROM employee_records USE INDEX (idx_username) WHERE username = 'alice';

-- Exclude index
EXPLAIN SELECT * FROM employee_records IGNORE INDEX (idx_old) WHERE department = 'IT';

-- Force index usage
EXPLAIN SELECT * FROM employee_records FORCE INDEX (idx_username) WHERE username = 'bob';

Index Condition Pushdown (ICP)

ICP optimizes queries by evaluating additional filter conditions in the storage engine layer, reducing bookmrak lookups. This shifts processing from the server to the storage engine, improving efficiency.