MySQL Query Optimizer
Optimize MySQL queries with AI-powered execution plan analysis, index recommendations, SQL tuning, and database performance diagnostics.
$9.90
Works with the AI Agents you already use
MySQL Query Optimizer
Example session with this skill installed
I have a MySQL 8 database with a large loan_application table containing approximately 20 million records.
The following query is taking several seconds to execute:
SELECT
la.id,
la.application_number,
la.customer_id,
la.loan_status,
la.created_at
FROM loan_application la
WHERE la.partner_id = 1001
AND la.loan_status IN ('APPROVED', 'PENDING')
AND la.created_at >= '2026-01-01'
ORDER BY la.created_at DESC
LIMIT 50 OFFSET 5000;
Please analyze this query as a database performance engineer.
I want you to
- Identify potential performance bottlenecks.
- Explain which predicates may benefit from indexing.
- Recommend an appropriate composite index and explain the column ordering.
- Analyze the impact of ORDER BY and OFFSET pagination.
- Suggest an alternative pagination strategy if appropriate.
- Identify potential filesort and temporary table operations.
- Provide an optimized SQL version.
- Explain how to validate the changes using EXPLAIN ANALYZE.
- Explain the trade-offs of adding the recommended index.
- Provide a structured before-and-after performance validation plan.
Do not execute any schema changes. First provide your analysis and recommendations.
Ask me for the actual table definition, existing indexes and execution plan if additional evidence is required.
- Read your context and instructions
- Compiled the mysql query optimizer
MySQL Query Optimization Report
1. Executive Summary
Database: MySQL 8
Table: loan_application
Approximate records: 20 million
Query type: Filter + sort + pagination
Assessment: Optimization opportunities identified. Actual bottlenecks require execution-plan verification.
2. Potential Performance Issues
| Area | Potential issue | Priority |
|---|---|---|
| Indexing | No suitable composite index | High |
| Filtering | Multiple predicates | High |
| Sorting | ORDER BY created_at DESC | High |
| Pagination | OFFSET 5000 | Medium |
| Data retrieval | Selected columns | Low |
These are hypotheses until confirmed using the actual schema and execution plan.
3. Recommended Index
Candidate index for evaluation
CREATE INDEX idx_loan_partner_status_created
ON loan_application (
partner_id,
loan_status,
created_at
);
Why this index?
- partner_id supports equality filtering.
- loan_status supports status filtering.
- created_at supports date-range filtering and may assist ordering.
Important: Because loan_status uses an IN condition with multiple values, MySQL may need additional sorting or merging. This index is a candidate, not a guaranteed optimal solution. Compare it with alternatives using actual execution plans.
4. Pagination Optimization
The existing query uses
LIMIT 50 OFFSET 5000;
For large offsets, the database may need to process and discard many qualifying rows.
Consider keyset pagination using the last returned created_at and id values.
Example
SELECT
id,
application_number,
customer_id,
loan_status,
created_at
FROM loan_application
WHERE partner_id = 1001
AND loan_status IN ('APPROVED', 'PENDING')
AND created_at >= '2026-01-01'
AND (
created_at < :last_created_at
OR (
created_at = :last_created_at
AND id < :last_id
)
)
ORDER BY created_at DESC, id DESC
LIMIT 50;
This requires a stable ordering and corresponding cursor handling. Verify the index and execution plan for the actual workload.
5. Validation Strategy
Before applying changes
- Capture SHOW CREATE TABLE.
- Capture SHOW INDEX.
- Run EXPLAIN on the original query.
- Run EXPLAIN ANALYZE in a safe environment.
- Record actual rows, execution time and access path.
- Test candidate indexes individually.
- Compare query results for correctness.
- Measure write overhead and index storage.
6. Expected Deliverables
- Query optimization assessment.
- Index recommendation.
- SQL rewrite.
- Pagination improvement.
- Execution-plan comparison.
- Validation checklist.
7. Final Recommendation
Do not add indexes based only on the query text.
Validate index selectivity, column cardinality, existing indexes and actual optimizer behavior before making production changes.
Status: Initial optimization assessment. No actual performance improvement has been measured yet.
Next step: Obtain the table DDL, existing indexes and EXPLAIN ANALYZE output to finalize the recommendation.
Connects securely to your tools. The creator never sees your data.
About this skill
MySQL Query Optimizer
Find database bottlenecks and improve MySQL query performance with a specialized AI-powered database engineering skill from Javaspace.
Slow SQL queries, inefficient indexes, unnecessary table scans and expensive joins can significantly affect application performance.
MySQL Query Optimizer helps developers analyze SQL queries, understand execution plans, identify indexing opportunities and generate practical optimization recommendations.
Key capabilities
- SQL Analysis: Review complex queries, joins, subqueries and aggregations.
- Execution Plan Analysis: Interpret EXPLAIN and EXPLAIN ANALYZE output.
- Index Optimization: Identify potential missing, redundant and inefficient indexes.
- JOIN Optimization: Review join conditions, cardinality and query execution patterns.
- Pagination Optimization: Identify inefficient OFFSET-based pagination.
- Large Table Optimization: Review queries involving millions of records.
- Performance Diagnostics: Investigate full table scans, filesorts and temporary tables.
- SQL Rewriting: Suggest optimized alternatives while preserving query behavior.
- Index Recommendations: Generate proposed composite indexes with trade-offs.
- Performance Reporting: Produce structured before-and-after validation reports.
Who should use it?
- Java and Spring Boot developers.
- Backend engineers.
- MySQL database developers.
- Technical leads and architects.
- Database performance engineers.
How it works
Provide your SQL query, table definitions, existing indexes and execution plan.
The skill analyzes the available evidence, identifies potential bottlenecks, recommends optimizations and provides a validation strategy.
Why Javaspace?
This skill follows a structured database engineering methodology rather than generating generic SQL suggestions.
It prioritizes evidence-based recommendations, query correctness, index efficiency and measurable performance improvements.
By Javaspace – Engineering expertise, packaged for AI coding agents.
How to use
- 1
Connect your agent to the Agensi MCP
Copy the prompt and paste it into Claude, ChatGPT, Cursor or any agent that supports MCP. It sets up the connection for you. Manual setup
- 2
Sign in to Agensi
Your agent shows a sign-in link. Sign in and click Allow.
- 3
Ask for what you need
Your agent loads this skill and uses it on your task. It always asks before any payment.
Reviews
No reviews yet
Be one of the first to try it. Every listed skill goes through the trust checks below.
Security scanned
Checked by our 8-point scan before listing
Fresh listing
Recently published to Agensi
Money-back guarantee
Doesn't do what its page promises? You get your money back.
Trust & safety
Security scan
Security scan passed 8 days ago
Requires
Permissions
Read access is required to inspect SQL files, database schemas and application query implementations. Terminal access may be used for SQL analysis and safe validation when an appropriate test database is explicitly configured. Write access is needed only when the user approves SQL or application code modifications. No production database credentials, cloud accounts or environment secrets are required by default. Never execute destructive SQL or production schema changes without explicit approval.