Research local slow query analysis and EXPLAIN-based guidance

Summary

Research and design a local slow query analyzer with optional EXPLAIN-based guidance.

This should be treated as a design-first item because query capture and index recommendations can create significant safety, privacy, and compatibility risks.

Proposed scope

• Define how slow queries would be captured without enabling heavy tracing globally.
• Explore temporary sampling modes for admin requests, frontend requests, and targeted URL tests.
• Evaluate local EXPLAIN analysis for selected queries.
• Define what guidance can be safely generated:
  • missing index hints
  • repeated meta-query warnings
  • full table scan indicators
  • filesort/temp table indicators
• Define what actions remain manual versus automated.

Safety requirements

• Do not store full query values when they may contain private content.
• Do not automatically add indexes in the first version.
• Do not enable SAVEQUERIES globally by default.
• Keep sampling temporary, bounded, and administrator-triggered.
• Multisite and large database behavior must be explicit.

Acceptance criteria

• A technical design documents capture method, sampling overhead, privacy filtering, UI, and limitations.
• The first implementation plan is diagnostic-only.
• Automated schema/index changes are explicitly out of scope unless separately approved.
• The design includes validation for high-query sites, WooCommerce sites, multisite, and disabled database permissions.

The issue names no files, tests, or entry points. Start by mapping the repository's existing query capture and administrative surfaces, then document bounded sampling, privacy filtering, EXPLAIN-based diagnostics, UI, limitations, and validation for the listed site types; done means a diagnostic-only technical design with automated schema changes explicitly out of scope.