Enhancement: Add `EXPLAIN` and `EXPLAIN EXTENDED` query analysis tools to MariaDB MCP Server

[rocknroll17]

Summary

Request to add explain_query and explain_query_extended tools to the MariaDB MCP Server to enable query performance analysis and optimization capabilities.

Motivation

User databases can range from simple to extremely complex and massive in scale. A single line difference in a query can drastically impact performance - transforming a query from executing in milliseconds to running indefinitely or timing out. This performance variance makes query optimization tools essential for database administrators and developers.

Problem Statement

Currently, the MariaDB MCP Server lacks built-in query performance analysis capabilities. Users working with large, complex databases need tools to:

• Analyze query execution plans
• Identify performance bottlenecks
• Optimize queries through techniques like subquery restructuring
• Prevent resource-intensive queries from impacting system performance

Proposed Solution

Add two new MCP tools:

1. explain_query

• Executes EXPLAIN on SQL queries to show basic execution plans
• Helps identify table access methods, index usage, and join strategies
• Returns execution plan details including estimated rows examined

2. explain_query_extended

• Executes EXPLAIN EXTENDED for detailed execution analysis
• Provides additional information like filtered row percentages
• Offers comprehensive performance insights for complex optimization scenarios

Use Cases

1. Query Optimization: Transform inefficient queries into optimized subqueries or restructured joins
2. Performance Monitoring: Identify queries that may cause performance degradation
3. Index Analysis: Determine if proper indexes are being utilized
4. Resource Planning: Estimate query resource consumption before execution

Expected Benefits

• Prevention of System Overload: Analyze before executing potentially expensive queries
• Improved Query Performance: Enable systematic query optimization workflows
• Better Database Administration: Provide essential tools for database performance management
• Enhanced Development Experience: Help developers write more efficient queries

Implementation Considerations

• Tools should support parameterized queries for security
• Should respect read-only mode restrictions
• Need proper error handling for invalid queries
• Should integrate seamlessly with existing MCP tool ecosystem

Related Features

These tools complement the existing execute_sql functionality by providing performance analysis capabilities that are essential for production database management.

This feature is considered essential for any comprehensive database MCP server, as query performance analysis is a fundamental requirement for managing complex database systems effectively.