get_queries_not_using_covering_index
Identify queries that perform index scans but require additional fetches, indicating they're not using covering indexes. Helps optimize database performance by finding inefficient queries.
Instructions
Get queries that don't use a covering index from the system:completed_requests catalog.
Args:
limit: Number of queries to return (default: 10)
Returns:
List of queries that perform index scans but also require fetches (not covering)
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| limit | No |
Implementation Reference
- src/tools/query.py:302-331 (handler)The main handler function for the 'get_queries_not_using_covering_index' tool. It executes a SQL++ query on system:completed_requests to find queries that perform index scans (phaseCounts.indexScan present) but also require document fetches (phaseCounts.fetch present), indicating non-covering indexes. Results are ordered by resultCount DESC, limited by the input limit, and handled with a custom empty message via the helper function.def get_queries_not_using_covering_index( ctx: Context, limit: int = 10 ) -> list[dict[str, Any]]: """Get queries that don't use a covering index from the system:completed_requests catalog. Args: limit: Number of queries to return (default: 10) Returns: List of queries that perform index scans but also require fetches (not covering) """ query = """ SELECT * FROM system:completed_requests WHERE phaseCounts.`indexScan` IS NOT MISSING AND phaseCounts.`fetch` IS NOT MISSING AND UPPER(statement) NOT LIKE '% SYSTEM:%' ORDER BY resultCount DESC LIMIT $limit """ return _run_query_tool_with_empty_message( ctx, query, limit=limit, empty_message=( "No queries that require fetches after index scans were found " "in system:completed_requests." ), )
- src/tools/__init__.py:42-64 (registration)The tool is registered by being imported from src/tools/query.py and included in the ALL_TOOLS list, which is used for MCP tool registration.ALL_TOOLS = [ get_buckets_in_cluster, get_server_configuration_status, test_cluster_connection, get_scopes_and_collections_in_bucket, get_collections_in_scope, get_scopes_in_bucket, get_document_by_id, upsert_document_by_id, delete_document_by_id, get_schema_for_collection, run_sql_plus_plus_query, get_index_advisor_recommendations, list_indexes, get_cluster_health_and_services, get_queries_not_selective, get_queries_not_using_covering_index, get_queries_using_primary_index, get_queries_with_large_result_count, get_queries_with_largest_response_sizes, get_longest_running_queries, get_most_frequent_queries, ]
- src/tools/query.py:108-127 (helper)Helper function used by get_queries_not_using_covering_index (and other query tools) to execute cluster queries and return a standardized empty result payload if no results are found.def _run_query_tool_with_empty_message( ctx: Context, query: str, *, limit: int, empty_message: str, extra_payload: dict[str, Any] | None = None, **query_kwargs: Any, ) -> list[dict[str, Any]]: """Execute a cluster query with a consistent empty-result response.""" results = run_cluster_query(ctx, query, limit=limit, **query_kwargs) if results: return results payload: dict[str, Any] = {"message": empty_message, "results": []} if extra_payload: payload.update(extra_payload) return [payload]