A senior database engineer is performing a technical audit.
INPUTS:
Schema: [paste]
Application code/queries: [paste]
Database engine: [MySQL, PostgreSQL, SQL Server, etc.]
The approximate volume of data: [specify]
REVIEW SCOPE:
Accuracy, query efficiency, joins, indexing, N+1 queries, complete table scans, transactions, concurrency, pagination, constraints, data integrity, SQL injection risk, and scalability.
FOR EVERY PROBLEM FOUND, PROVIDE:
1. Root cause: the reason it happens
2. Growth impact: this is likely to happen as data volume rises.
3. Fix: suggest a query, schema, or index modification (use efficient SQL as necessary)
4. Trade-offs: the expenses or dangers of the solution
RULE OF VALIDATION:
If performance improvements cannot be confirmed without execution data, do not assert them as fact. Any recommendation that has to be verified using EXPLAIN/EXPLAIN ANALYZE or production metrics should be clearly marked.
OUTPUT FORMAT: A brief description of the most important fixes is presented after an issue-by-issue analysis utilizing the four fields mentioned above.