MySQL offers several native tools for query profiling and performance analysis:

-- Enable profiling for the session
SET profiling = 1;

-- Execute your query
SELECT * FROM large_table WHERE complex_condition = 1;

-- View profiling results
SHOW PROFILE;

-- Get detailed query execution information
SHOW PROFILE ALL FOR QUERY 1;

-- See profile for specific metrics
SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;

The Performance Schema provides low-level instrumentation:

-- Check if Performance Schema is enabled
SHOW VARIABLES LIKE 'performance_schema';

-- Enable specific instruments (if needed)
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'statement/%' OR NAME LIKE 'stage/%';

-- Analyze statement events
SELECT * FROM performance_schema.events_statements_history_long
ORDER BY TIMER_WAIT DESC LIMIT 10;

-- View table I/O statistics
SELECT * FROM performance_schema.table_io_waits_summary_by_table
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

MySQL 8.0+ provides execution plan analysis with actual metrics:

EXPLAIN ANALYZE
SELECT c.name, COUNT(o.id) 
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE c.status = 'active'
GROUP BY c.name
ORDER BY COUNT(o.id) DESC;

Several specialized tools offer enhanced profiling:

• Percona PMM: Comprehensive monitoring with Query Analytics dashboard
• MySQL Enterprise Monitor: Includes Performance Advisor
• pt-query-digest: For analyzing slow query logs
• ProxySQL: With built-in query logging and analysis

Configure and analyze slow queries:

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- seconds
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- Process the log with pt-query-digest
# pt-query-digest /var/lib/mysql/mysql-slow.log > slow_report.txt

MySQL Workbench provides visual tools:

-- Performance Reports section shows:
-- SQL Statistics
-- Table Statistics
-- User Statistics
-- System Statistics

Create a profiling wrapper for ad-hoc testing:

DELIMITER //
CREATE PROCEDURE profile_query(IN query_text TEXT)
BEGIN
    DECLARE start_time BIGINT;
    SET start_time = (SELECT UNIX_TIMESTAMP(NOW(6)) * 1000000);
    
    SET @sql = query_text;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    SELECT 
        query_text AS 'Query',
        (SELECT UNIX_TIMESTAMP(NOW(6)) * 1000000) - start_time AS 'Duration (μs)',
        LAST_INSERT_ID() AS 'Last Insert ID',
        ROW_COUNT() AS 'Rows Affected';
END //
DELIMITER ;

-- Usage example:
CALL profile_query('SELECT * FROM large_table WHERE id BETWEEN 1000 AND 2000');

Track how MySQL optimizes your queries:

-- Enable optimizer trace
SET optimizer_trace="enabled=on";
SET optimizer_trace_max_mem_size=1000000;

-- Execute problematic query
SELECT * FROM problematic_table WHERE complex_conditions;

-- View the trace
SELECT * FROM information_schema.optimizer_trace;

Profiling MySQL queries involves tracking execution metrics to identify performance bottlenecks. Unlike MSSQL's built-in profiler, MySQL requires a combination of native tools and third-party solutions.

The most straightforward approach uses MySQL's built-in capabilities:

-- Enable profiling for current session
SET profiling = 1;

-- Execute your queries
SELECT * FROM large_table WHERE complex_condition;

-- View profiling data
SHOW PROFILES;

-- Detailed profile for specific query
SHOW PROFILE FOR QUERY 1;

MySQL 5.6+ introduced Performance Schema for detailed monitoring:

-- Enable performance schema
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_statements%';

-- Query execution statistics
SELECT * FROM performance_schema.events_statements_summary_by_digest 
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

The EXPLAIN command reveals how MySQL executes queries:

EXPLAIN FORMAT=JSON
SELECT customers.* FROM customers
JOIN orders ON customers.id = orders.customer_id
WHERE orders.total > 1000;

Several powerful tools extend MySQL's native capabilities:

• Percona PMM: Comprehensive monitoring with query analytics
• MySQL Enterprise Monitor: Oracle's commercial solution
• pt-query-digest: Part of Percona Toolkit for slow log analysis

Here's a complete example using slow query log analysis:

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- Log queries >1 second
SET GLOBAL log_output = 'FILE';

-- After running application queries:
pt-query-digest /var/lib/mysql/yourhost-slow.log

-- Output shows query patterns, execution times, and recommendations

Consider this problematic query and its optimization:

-- Original query (takes 4.2s)
SELECT * FROM products WHERE category LIKE '%electronics%';

-- Optimized version (0.03s) using full-text index
ALTER TABLE products ADD FULLTEXT(category);
SELECT * FROM products WHERE MATCH(category) AGAINST('electronics');

For production environments, implement ongoing profiling:

1. Set up Performance Schema with 1-minute granularity
2. Configure alerts for queries exceeding threshold times
3. Regularly review query execution plans
4. Track historical performance trends