https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42585 --- Comment #19 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 207242 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=207242&action=edit Bug 42585: Add EXPLAIN-based analyzers This patch adds five checks that read the EXPLAIN plan instead of the SQL text: * large_temp_table_risk, a temp table bigger than the server's in memory cap, so it spills to disk * uses_join_buffer, the planner picked a Block Nested Loop because there's no index on the join column * dependent_subquery, the planner expects to run the subquery again for every outer row * full_table_scan, an access type of ALL on a table that isn't small * low_filter_efficiency, a full scan that keeps less than 10% of the rows it reads Scans of small lookup tables like branches and authorised_values are skipped outright. The scan checks are scale dependent on top of that, so a query the plan can bound -- an indexed WHERE, or a LIMIT the plan can stream straight to -- doesn't get warned about either. Test Plan: 1) Apply this patch 2) prove -r t/Koha/Reports/Analyzer/Check/Explain/ 3) Analyze a report with the SQL "SELECT b.borrowernumber, ( SELECT COUNT(*) FROM issues i WHERE i.borrowernumber = b.borrowernumber ) FROM borrowers b" 4) Note the dependent_subquery finding, listed under "Not a problem on this database" with the row estimate that decided it! 5) Analyze a report with the SQL "SELECT * FROM items", note the full_table_scan finding once items is big enough to matter! 6) Add a LIMIT 10 to it and analyze again, note the finding moves to "Not a problem on this database" because the plan only reads the 10 rows asked for! 7) Analyze a report with the SQL "SELECT borrowernumber FROM borrowers WHERE borrowernumber = 1" 8) Note there are no EXPLAIN findings at all! -- You are receiving this mail because: You are watching all bug changes.