Identifying Slow Queries and Common Performance Bottlenecks in MySQL

Course: MySQL Training

Topic: Identifying Slow Queries and Common Performance Bottlenecks

Course Link: MySQL Training Course

Demo Link: Register for a Course Demo

1. Introduction to MySQL Query Performance

MySQL is a relational database management system used to store, retrieve, and manage structured data. As an application grows, database tables become larger and queries may take longer to execute. Slow queries can make websites unresponsive, delay reports, increase server resource usage, and reduce the overall user experience.

Identifying slow queries means finding SQL statements that take too long to execute or consume excessive resources. Identifying performance bottlenecks means determining which part of the database system is limiting performance, such as inefficient SQL, missing indexes, disk input/output, CPU usage, memory pressure, locks, or too many concurrent connections.

Performance optimization should begin with measurement and diagnosis rather than randomly changing queries or adding indexes. The goal is to understand where time is being spent, identify the underlying cause, apply an appropriate improvement, and measure the result.

1.1 Learning Objectives

• Understand slow queries and database performance bottlenecks.
• Recognize common causes of slow MySQL queries.
• Enable and analyze the MySQL slow query log.
• Use EXPLAIN and EXPLAIN ANALYZE to investigate query execution plans.
• Identify missing indexes, full table scans, expensive joins, and inefficient sorting.
• Monitor database activity using Performance Schema and relevant system views.
• Recognize CPU, memory, disk, connection, and locking bottlenecks.
• Optimize queries and verify performance improvements safely.

1.2 What Is a Slow Query?

A slow query is an SQL statement that takes longer than an acceptable amount of time to complete or consumes more database resources than expected. Whether a query is considered slow depends on the application, data volume, workload, and performance requirements.

For example, a product search that takes 50 milliseconds may be acceptable for one application, while a customer-facing operation that consistently takes several seconds may require investigation.

1.3 What Is a Performance Bottleneck?

A performance bottleneck is a resource or operation that limits the speed or throughput of a database workload. Even a well-written query may run slowly if the server is overloaded or waiting for a locked row.

2. Common Causes of Slow MySQL Queries

2.1 Missing or Inefficient Indexes

An index helps MySQL locate rows without reading an entire table. When a query filters, joins, sorts, or groups data without a suitable index, MySQL may examine many rows before producing the result.

Consider the following query:

SELECT *
FROM orders
WHERE customer_id = 105;

If the orders table contains millions of rows and customer_id has no suitable index, MySQL may perform a full table scan. An index may help:

CREATE INDEX idx_orders_customer_id
ON orders (customer_id);

This is not a guarantee that the index will be used. The optimizer considers table size, data distribution, selectivity, query conditions, and estimated costs. Before creating an index, check existing indexes and the execution plan.

2.2 Retrieving Too Many Rows

Returning thousands of unnecessary records increases database work, network traffic, application processing, and memory consumption.

Less efficient example:

SELECT *
FROM customers;

Improved example when only a few columns are required:

SELECT customer_id, customer_name, email
FROM customers
WHERE status = 'active'
LIMIT 100;

The LIMIT clause restricts the number of returned rows, but it does not necessarily prevent MySQL from examining many rows before finding them. A suitable index and selective filtering may still be necessary.

2.3 Using SELECT * Unnecessarily

The asterisk returns every column in a table or result set. This may increase disk reads, network transfer, and application processing, especially when tables contain large text, JSON, or binary columns.

Prefer selecting only the columns required by the application:

-- Less specific
SELECT *
FROM products
WHERE product_id = 10;

-- More specific
SELECT product_id, product_name, price
FROM products
WHERE product_id = 10;

2.4 Inefficient JOIN Operations

JOIN operations combine rows from related tables. Queries may become slow when join columns are not indexed appropriately, join conditions are incorrect, or the query produces a very large intermediate result.

SELECT c.customer_name, o.order_date, o.total_amount
FROM customers AS c
JOIN orders AS o
  ON c.customer_id = o.customer_id
WHERE o.order_date >= '2026-01-01';

Investigate indexes on the join columns and filter columns. A composite index may be helpful depending on the query and data distribution. Do not add indexes without examining the execution plan and write workload.

2.5 Inefficient Sorting and Grouping

ORDER BY and GROUP BY can require sorting or temporary intermediate results. Large result sets may be expensive to sort, particularly when no appropriate index can support the operation.

SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = 105
ORDER BY order_date DESC
LIMIT 20;

An index beginning with customer_id and followed by order_date may be worth evaluating for this query. The best index depends on the actual workload, sort direction, data distribution, and execution plan.

2.6 Functions Applied to Indexed Columns

Applying a function to an indexed column can prevent efficient use of a conventional index for a search condition, depending on the expression and available indexes.

-- May prevent efficient use of a normal index on order_date
SELECT order_id
FROM orders
WHERE YEAR(order_date) = 2026;

-- Often more index-friendly
SELECT order_id
FROM orders
WHERE order_date >= '2026-01-01'
  AND order_date < '2027-01-01';

Range conditions are generally preferable when they express the intended filter. MySQL also supports functional indexes in applicable versions, so evaluate the alternatives against your MySQL version and query plan.

2.7 Leading Wildcards in LIKE Conditions

A pattern that starts with a wildcard often prevents a conventional B-tree index from efficiently locating matching text values.

-- A leading wildcard often prevents a normal B-tree index range lookup
SELECT product_name
FROM products
WHERE product_name LIKE '%phone';

-- A prefix search can often use a suitable index
SELECT product_name
FROM products
WHERE product_name LIKE 'phone%';

For substring searching across large text collections, consider whether FULLTEXT indexing or a dedicated search system better matches the requirements.

2.8 Large OFFSET Values

Pagination with a very large OFFSET may require MySQL to identify and discard many rows before returning the requested page.

SELECT order_id, order_date
FROM orders
ORDER BY order_id
LIMIT 50 OFFSET 100000;

For deep pagination, consider keyset pagination when suitable:

SELECT order_id, order_date
FROM orders
WHERE order_id > 100000
ORDER BY order_id
LIMIT 50;

This example assumes order_id provides a suitable, consistently ordered cursor. For other sort orders, use a stable ordering and a matching cursor condition, potentially including multiple columns.

3. Understanding Query Performance Metrics

Query duration is important, but it does not explain every performance problem. Several measurements can help identify the source of a slowdown.

4. Identifying Slow Queries with the Slow Query Log

The MySQL slow query log records SQL statements that exceed a configured execution-time threshold and can also record statements that examine many rows, depending on configuration. It is a key starting point for finding queries that deserve investigation.

4.1 Check the Current Configuration

Use the following statements to inspect the slow query log settings:

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_output';
SHOW VARIABLES LIKE 'min_examined_row_limit';

The slow query log may be disabled by default in some configurations. Logging destinations, privileges, and available variables depend on the MySQL version and deployment environment.

4.2 Enable the Slow Query Log

With the necessary administrative privileges, the log can be enabled dynamically:

SET GLOBAL slow_query_log = 'ON';

Set an appropriate threshold for investigation:

SET GLOBAL long_query_time = 1;

This sets the global threshold to one second for applicable new sessions. Existing sessions may retain their session-level setting, and changes made dynamically may not persist after a server restart. Use the configuration file or supported persisted-variable mechanisms if you need a durable setting.

For example, a configuration file may contain settings like these:

[mysqld]
slow_query_log=ON
long_query_time=1
log_output=FILE

Confirm the configuration path and supported options for the installed MySQL version. Avoid enabling verbose logging indiscriminately on a busy production server without considering disk usage and operational impact.

4.3 Log Queries That Examine Many Rows

MySQL can be configured to include queries that examine at least a specified number of rows, even when their execution time is below the main threshold.

SET GLOBAL min_examined_row_limit = 1000;

Choose a threshold appropriate for the workload. The setting is not a universal recommendation, and it should be adjusted based on the amount of data, query patterns, and logging volume.

4.4 Review the Slow Query Log

When the log output is FILE, identify the configured file path and use appropriate operating-system tools or database administration tools to inspect it. A command-line summary can help aggregate repeated statements:

mysqldumpslow -s t -t 10 /path/to/slow-query.log

This example summarizes the ten query patterns with the highest total query time under the selected sort mode. Confirm the exact options supported by the installed utility.

Look for repeated query patterns, high execution times, large row counts, and statements that become slower as the data grows. The log is a diagnostic starting point; it does not automatically explain why a query is slow.

4.5 Important Slow Log Considerations

• Set a threshold that captures meaningful problems without producing excessive logs.
• Check available disk space and log rotation practices.
• Protect logs because SQL statements may contain sensitive information.
• Use a staging environment when testing configuration changes where possible.
• Compare slow queries with application traces and server metrics to understand the wider context.

5. Analyzing Queries with EXPLAIN

EXPLAIN displays the execution plan MySQL expects to use for a query. It can show the access method, possible indexes, selected index, estimated rows, and other details that help identify expensive operations.

5.1 Basic EXPLAIN Syntax

EXPLAIN
SELECT order_id, customer_id, total_amount
FROM orders
WHERE customer_id = 105;

EXPLAIN is useful before changing an index or rewriting a query. It provides estimates and plan details, not a complete measurement of actual execution time.

5.2 Important EXPLAIN Columns

5.3 Understanding Access Types

EXPLAIN may show different access types. Their suitability depends on the query and table size, so no single type should be judged in isolation.

A full table scan is not always a problem. For a small table or a query that must read a large percentage of the table, scanning may be more efficient than using an index. Investigate the number of rows, selectivity, and actual execution time before deciding.

5.4 Recognizing Filesort and Temporary Tables

EXPLAIN output may include Extra information such as Using filesort or Using temporary. These indicate that MySQL performs sorting or uses a temporary table for part of the execution.

These indicators do not automatically mean that the query is poorly optimized. They become more concerning when the operation processes a large amount of data or contributes significantly to latency.

5.5 Using EXPLAIN ANALYZE

On supported MySQL versions, EXPLAIN ANALYZE executes the statement and reports actual execution information alongside the plan. It can help compare estimated row counts with actual row counts and identify where execution time is spent.

EXPLAIN ANALYZE
SELECT order_id, total_amount
FROM orders
WHERE customer_id = 105;

Because EXPLAIN ANALYZE executes the statement, use it carefully. Start with safe SELECT statements, and consider the cost of execution on large datasets or production systems. For write statements or expensive operations, test in an appropriate environment before using diagnostic commands.

6. Identifying Index-Related Bottlenecks

6.1 Inspect Existing Indexes

Use SHOW INDEX to understand the indexes already defined on a table:

SHOW INDEX FROM orders;

Review index columns, column order, uniqueness, cardinality estimates, and whether the existing indexes match frequent filters, joins, and sorting operations.

6.2 Understand Composite Indexes

A composite index contains more than one column. The order of columns matters because MySQL can generally use the leftmost prefix of a composite B-tree index for relevant lookups.

CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date);

This index may help queries that filter by customer_id and may also support suitable queries involving customer_id and order_date. It is not equivalent to having an independent index on order_date for every query pattern.

6.3 Avoid Excessive Indexing

Indexes can improve reads, but they consume storage and require maintenance when rows are inserted, updated, or deleted. Too many indexes may slow write-heavy workloads and increase operational costs.

• Review existing indexes before creating new ones.
• Use execution plans and representative workload tests to justify each index.
• Check whether an existing composite index already covers the required columns.
• Evaluate write performance and storage costs after adding an index.
• Do not remove an index simply because it appears unused in a short observation period.

6.4 Refresh Optimizer Statistics When Appropriate

The optimizer uses statistics to estimate the cost of different execution plans. Outdated or inaccurate statistics can contribute to poor plan choices.

ANALYZE TABLE orders;

ANALYZE TABLE updates table statistics according to the storage engine and MySQL configuration. Consider the effect on the workload and use the appropriate maintenance process for production systems.

7. Monitoring with Performance Schema and System Views

Performance Schema collects server instrumentation and statement statistics. It helps investigate what the database is doing, which statements consume time, and where resources may be spent. The sys schema provides convenient views built on performance-related data.

7.1 Find Statements with High Total Execution Time

The following example uses a Performance Schema summary table to find statement patterns with high accumulated execution time:

SELECT
    DIGEST_TEXT,
    COUNT_STAR AS execution_count,
    ROUND(SUM_TIMER_WAIT / 1000000000000, 3) AS total_seconds,
    ROUND(AVG_TIMER_WAIT / 1000000000000, 6) AS average_seconds,
    SUM_ROWS_EXAMINED AS rows_examined,
    SUM_ROWS_SENT AS rows_sent
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

The timer values in this Performance Schema summary are represented in picoseconds, so dividing by 1,000,000,000,000 converts them to seconds. Available columns and collection behavior should be verified against the installed MySQL version.

High total time may mean a query is very slow each time or that a moderately expensive query runs extremely frequently. Compare total time, average time, execution count, and rows examined before prioritizing a fix.

7.2 Find Queries Examining Many Rows

SELECT
    DIGEST_TEXT,
    COUNT_STAR AS execution_count,
    SUM_ROWS_EXAMINED AS total_rows_examined,
    SUM_ROWS_SENT AS total_rows_sent
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IS NOT NULL
ORDER BY SUM_ROWS_EXAMINED DESC
LIMIT 10;

A large difference between rows examined and rows returned can indicate weak filtering or a plan that reads more data than necessary. However, some analytical queries legitimately process many rows, so interpret results in context.

7.3 Use sys Schema Views

On installations where the relevant sys schema views are available and populated, they provide simplified summaries for investigation.

SELECT *
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 10;

The sys.statement_analysis view summarizes statement activity and latency. Review its column definitions and availability for your version. Access may require appropriate privileges.

7.4 Check Current Database Activity

The process list shows active sessions and their current states:

SHOW FULL PROCESSLIST;

Look for queries running unusually long, sessions waiting on locks, idle connections, or repeated statements. A query's State and Time fields are clues, not a complete diagnosis. Avoid terminating sessions without understanding the operation and its consequences.

8. Common Server-Level Performance Bottlenecks

8.1 CPU Bottlenecks

High CPU utilization may result from complex joins, repeated expression evaluation, large sorts, inefficient queries, or excessive concurrent workload.

Diagnostic approach:

1. Check CPU usage on the database host.
2. Identify statements with high total and average execution time.
3. Review EXPLAIN output and actual execution measurements where available.
4. Reduce unnecessary row processing and expensive repeated operations.
5. Retest the workload after each meaningful change.

8.2 Memory Pressure

MySQL uses memory for structures such as the InnoDB buffer pool, connection-related buffers, and internal operations. If frequently accessed data does not fit effectively in available memory, disk reads may increase. Excessive per-session memory use can also create pressure when many operations run concurrently.

• Review memory utilization and operating-system swapping.
• Evaluate the InnoDB buffer pool size relative to available system memory.
• Inspect connection counts and concurrency.
• Avoid increasing per-query or per-connection buffers without measuring the impact.
• Leave sufficient memory for the operating system and other processes.

8.3 Disk I/O Bottlenecks

Disk performance can limit query execution when MySQL must read data from storage frequently or when write activity is heavy. Slow storage can affect reads, writes, temporary work, and transaction commits.

Possible causes include:

• Large scans that read many pages.
• A workload with frequent writes or large transactions.
• Insufficient memory for the working set.
• Storage with high latency or limited throughput.
• Temporary data spilling to disk.

Review storage latency, I/O utilization, InnoDB metrics, and query plans together. Adding memory or changing storage settings should be based on observed evidence.

8.4 Connection and Concurrency Bottlenecks

Too many concurrent sessions can increase memory consumption, CPU contention, scheduling overhead, and lock conflicts. Applications that open a new database connection for every request without reuse may create unnecessary overhead.

Useful practices include:

• Use a suitable connection pool in the application.
• Monitor active connections and connection errors.
• Set connection limits according to available resources and workload.
• Keep transactions short and avoid unnecessary work while holding locks.
• Investigate long-running sessions and idle transactions.

8.5 Lock Contention and Deadlocks

Concurrent transactions may need to access the same rows or index records. When one transaction holds a conflicting lock, another transaction may wait. Deadlocks occur when transactions create a cycle of dependencies that prevents all of them from proceeding.

Inspect InnoDB status when investigating locking problems:

SHOW ENGINE INNODB STATUS;

Review transaction duration, lock waits, deadlock information, and application transaction boundaries. Common improvements include keeping transactions short, accessing shared resources in a consistent order, and indexing update and delete conditions appropriately.

Do not assume that every slow query is caused by its execution plan. A query may spend most of its elapsed time waiting for a lock rather than processing rows.

9. Diagnosing Temporary Tables, Sorting, and Grouping

Queries involving DISTINCT, GROUP BY, ORDER BY, and complex joins may need intermediate processing. Depending on the query and execution plan, MySQL may use temporary tables or sorting operations.

9.1 Identify Expensive Operations

EXPLAIN
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
ORDER BY order_count DESC;

Review the execution plan for estimated row counts, index usage, and Extra details. If the query processes a large portion of the table, a scan may be reasonable; the important question is whether the work meets the application's performance requirements.

9.2 Improve Aggregation Queries

• Filter rows before aggregation when the business logic permits.
• Select only the columns required by the result.
• Evaluate indexes that support filters and grouping.
• Avoid repeating the same expensive aggregation unnecessarily.
• Consider summary tables or pre-aggregated data for frequently requested reports when appropriate.

Pre-aggregation introduces additional storage, refresh, and consistency considerations. Choose it only when the read-performance benefit justifies the complexity.

10. A Step-by-Step Slow Query Investigation Workflow

1. Confirm the problem: Identify which application operation is slow and whether the issue is consistent or intermittent.
2. Measure latency: Record response time, query frequency, and workload conditions.
3. Find the SQL statement: Use the slow query log, application tracing, or Performance Schema summaries.
4. Check server health: Examine CPU, memory, disk I/O, connection counts, and lock waits.
5. Run EXPLAIN: Review table access methods, selected indexes, estimated rows, and sorting or temporary operations.
6. Measure actual execution: Where supported and safe, use EXPLAIN ANALYZE on an appropriate query.
7. Identify the root cause: Determine whether the problem is SQL design, indexing, data volume, locking, resource saturation, or another factor.
8. Make one targeted change: Adjust the query, index, schema, or configuration based on the evidence.
9. Test with representative data: Compare execution time and resource usage under realistic conditions.
10. Verify correctness: Confirm that results, ordering, filtering, and transaction behavior remain correct.
11. Monitor after deployment: Confirm that the improvement persists and does not introduce regressions elsewhere.

11. Practical Example: Optimizing a Slow Customer Orders Query

11.1 Scenario

Assume an e-commerce application stores millions of rows in an orders table. The application frequently displays the latest orders for a specific customer.

11.2 Initial Query

SELECT *
FROM orders
WHERE customer_id = 105
ORDER BY order_date DESC
LIMIT 20;

Potential concerns include selecting unnecessary columns, scanning too many rows to find the customer's orders, and sorting a large matching result set.

11.3 Inspect the Execution Plan

EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 105
ORDER BY order_date DESC
LIMIT 20;

Check whether MySQL uses an appropriate index, how many rows it estimates reading, and whether an additional sort is needed.

11.4 Evaluate a Composite Index

CREATE INDEX idx_orders_customer_orderdate
ON orders (customer_id, order_date);

This index may support filtering by customer and retrieving rows in order-date order. Whether it eliminates sorting or improves execution depends on the optimizer's chosen plan and the query's exact requirements.

11.5 Reduce the Selected Columns

SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = 105
ORDER BY order_date DESC
LIMIT 20;

Selecting fewer columns can reduce data transfer and processing. If the result must be deterministic when multiple orders share the same date, add a unique tie-breaker such as order_id to the ORDER BY clause and evaluate an index that supports that ordering.

11.6 Compare Before and After

Do not assume a fixed percentage improvement. The actual result depends on table size, data distribution, storage, cache state, concurrency, and the query plan.

12. Common Mistakes During Performance Troubleshooting

• Adding indexes without analysis: Indexes have storage and write-maintenance costs. Use the query plan and workload evidence first.
• Optimizing only average latency: Averages can hide severe slowdowns. Review percentiles and outliers when application monitoring provides them.
• Ignoring query frequency: A moderately expensive query executed thousands of times may consume more total resources than a rare slow query.
• Assuming ALL is always bad: A full scan may be the best plan for small tables or queries reading a large share of the data.
• Running expensive tests on production without safeguards: Diagnostic queries can consume substantial resources. Use controlled tests where possible.
• Changing many things at once: Multiple simultaneous changes make it difficult to identify which change affected performance.
• Ignoring locking: Query latency can be caused by waiting for other transactions, not just inefficient SQL.
• Ignoring application behavior: Repeated queries, missing connection pooling, or fetching unnecessary data may be the real source of the issue.
• Failing to retest: Always measure the result and verify correctness after optimization.

13. Best Practices for MySQL Performance Monitoring

• Establish a baseline for normal query latency and resource usage.
• Use the slow query log and statement summaries to prioritize investigation.
• Review execution plans for high-impact queries before modifying indexes.
• Track both frequently executed queries and queries with very high individual latency.
• Monitor CPU, memory, disk I/O, connections, and transaction waits together.
• Keep SQL statements clear and retrieve only the data required.
• Design indexes based on actual filters, joins, sorting requirements, and workload patterns.
• Test schema and configuration changes against representative data.
• Protect logs and monitoring outputs that may contain sensitive data.
• Document changes and retain before-and-after measurements.
• Review performance after data volume, application traffic, or query patterns change.

14. Troubleshooting Reference Table

15. Practical Exercises

Exercise 1: Enable and Inspect the Slow Query Log

1. Check the slow query log and long_query_time variables.
2. Enable logging in a suitable test environment.
3. Run a deliberately inefficient query against sample data.
4. Inspect the resulting log entry and identify the query duration.
5. Document what the log reveals and what additional investigation is needed.

Exercise 2: Analyze a Query Plan

1. Create a sample orders table with a realistic amount of test data.
2. Run EXPLAIN on a query filtering by customer_id.
3. Record the access type, possible_keys, key, rows, and Extra fields.
4. Evaluate an appropriate index in the test environment.
5. Run EXPLAIN again and compare the plans.

Exercise 3: Find High-Cost Statement Patterns

1. Query Performance Schema statement summaries where permissions and instrumentation allow.
2. Sort statement patterns by total execution time.
3. Repeat the analysis by total rows examined.
4. Identify whether the highest-cost patterns are individually slow or frequently executed.
5. Choose one statement for further investigation.

Exercise 4: Investigate a Concurrency Problem

1. In a controlled test environment, open two sessions and perform transactions that contend for the same data.
2. Observe the waiting session and inspect the active process list.
3. Review InnoDB status for relevant locking information.
4. Commit or roll back the test transactions safely.
5. Explain how shorter transactions or a consistent access order could reduce contention.

16. Interview Questions and Answers

Q1. What is a slow query in MySQL?

A slow query is an SQL statement that takes longer than an acceptable threshold or consumes excessive resources for the workload. The acceptable threshold depends on application requirements and operating conditions.

Q2. What is the MySQL slow query log?

It is a diagnostic log that records queries meeting configured slow-query criteria. It helps identify expensive statements for further analysis.

Q3. What is EXPLAIN used for?

EXPLAIN displays the optimizer's expected execution plan, including access methods, possible and selected indexes, estimated rows, and other plan details.

Q4. What is the difference between EXPLAIN and EXPLAIN ANALYZE?

EXPLAIN provides plan estimates and details without normally executing the query. EXPLAIN ANALYZE, on supported versions, executes the statement and reports actual execution measurements alongside the plan.

Q5. Why can a missing index make a query slow?

Without a suitable index, MySQL may need to examine many rows to find the required records, increasing CPU work and data access.

Q6. Is a full table scan always bad?

No. A full scan may be efficient for small tables or when a query needs a large percentage of the table. Its cost should be evaluated in context.

Q7. What is a composite index?

A composite index contains multiple columns. Column order affects which filtering and ordering patterns the index can support efficiently.

Q8. What does Using filesort mean?

It indicates that MySQL performs an additional sorting operation for part of the query. It is not automatically a problem, but large sorts may be expensive.

Q9. How can Performance Schema help?

Performance Schema collects instrumented server activity and statement statistics. Its summaries can help identify query patterns with high total time, average time, or rows examined.

Q10. What is lock contention?

Lock contention occurs when concurrent transactions compete for incompatible locks on database resources, causing one or more operations to wait.

Q11. Why can SELECT * affect performance?

It may retrieve unnecessary columns, increasing data reads, network traffic, and application processing.

Q12. How do you verify a performance optimization?

Measure the original behavior, make a targeted change, retest under comparable conditions, compare execution plans and resource usage, and verify that query results remain correct.

17. Quick Revision Checklist

• Understand the difference between slow queries and server bottlenecks.
• Check the slow query log and its threshold settings.
• Use EXPLAIN to inspect access methods and index selection.
• Use EXPLAIN ANALYZE carefully when supported.
• Review indexes and composite-index column order.
• Look for unnecessary columns, large result sets, and expensive sorting.
• Check Performance Schema and sys schema summaries where available.
• Monitor CPU, memory, disk I/O, connection counts, and lock waits.
• Test one change at a time and compare results.
• Verify correctness and monitor the system after deployment.

18. Conclusion

Identifying slow queries and common performance bottlenecks is an essential part of maintaining reliable MySQL applications. Effective troubleshooting combines SQL analysis, execution plans, slow query logs, statement statistics, index evaluation, and server-level monitoring.

The most reliable approach is to measure first, identify the actual bottleneck, make a targeted change, and verify the result. A query may be slow because it reads too many rows, performs expensive sorting, waits for locks, or competes for limited server resources. Understanding the difference helps database developers and administrators choose the right solution instead of making unnecessary changes.

For structured learning, practical exercises, and additional course information, visit JustAcademy MySQL Training or register for a course demo.