References
QueryAudit's detection rules are grounded in official database documentation, peer-reviewed academic research, and established technical literature.
Academic Papers
SQL Anti-Pattern Detection
| Paper | Venue | Year | Relevance |
| Dintyala, P., Narechania, A., and Arulraj, J. "SQLCheck: Automated Detection and Diagnosis of SQL Anti-Patterns." | ACM SIGMOD | 2020 | Holistic anti-pattern detection framework with impact ranking and fix suggestions. (DOI) |
| Lyu, Y., Volokh, S., Halfond, W.G.J., and Tripp, O. "SAND: A Static Analysis Approach for Detecting SQL Antipatterns." | ACM ISSTA (Distinguished Paper Award) | 2021 | Static analysis achieving 99.4-100% precision across 1,000 applications. (DOI) |
| Nagy, C. and Cleve, A. "SQLInspect: A Static Analyzer to Inspect Database Usage in Java Applications." | IEEE/ACM ICSE | 2018 | Eclipse plug-in for embedded SQL analysis and code smell detection. (DOI) |
| Paper | Venue | Year | Relevance |
| Shao, S., Qiu, Z., Yu, X., Yang, W., Jin, G., Xie, T., and Wu, X. "Database-Access Performance Antipatterns in Database-Backed Web Applications." | IEEE ICSME | 2020 | Catalogs 34 performance antipatterns (24 known + 10 new) from real-world applications. (DOI) |
| Alshemaimri, B. and Elmasri, R. "A survey of problematic database code fragments in software systems." | Engineering Reports | 2021 | Categorization of SQL antipatterns by performance, maintainability, portability, and data integrity. (DOI) |
| Yagoub, K., et al. "Oracle's SQL Performance Analyzer." | IEEE Data Engineering Bulletin | 2008 | Query-aware testing by forecasting SQL plan changes across database upgrades. |
Official MySQL Documentation
Official PostgreSQL Documentation
Books
| Book | Authors | Publisher | Year | ISBN |
| SQL Antipatterns: Avoiding the Pitfalls of Database Programming | Karwin, B. | Pragmatic Bookshelf | 2010 | 978-1934356555 |
| High Performance MySQL, 3rd ed. | Schwartz, B., Zaitsev, P., Tkachenko, V. | O'Reilly Media | 2012 | 978-1449314286 |
| High-Performance Java Persistence | Mihalcea, V. | Leanpub | 2016 | -- |
Detection Rule to Reference Mapping
| Detection Rule | MySQL Docs | PostgreSQL Docs | Papers | Books |
| N+1 Query | -- | -- | Shao et al. (2020), Dintyala et al. (2020) | Mihalcea (2016) |
| SELECT * | -- | -- | Alshemaimri & Elmasri (2021), Lyu et al. (2021) | Karwin (2010) |
| WHERE Function | -- | -- | Dintyala et al. (2020) | Schwartz et al. (2012) |
| Missing Index | innodb-locking, innodb-locks-set | indexes-examine | Shao et al. (2020) | Schwartz et al. (2012) |
| UPDATE/DELETE without WHERE | mysql-tips, innodb-locks-set | -- | Dintyala et al. (2020) | Karwin (2010) |
| DML without Index | innodb-locks-set, innodb-locking | explicit-locking | Shao et al. (2020) | Schwartz et al. (2012) |
| Repeated Single INSERT | insert-optimization, bulk-data-loading, Connector/J | -- | -- | Mihalcea (2016) |
| INSERT ... SELECT * | insert-select | -- | Alshemaimri & Elmasri (2021) | Karwin (2010) |
| OFFSET Pagination | -- | -- | Shao et al. (2020) | Schwartz et al. (2012) |
| OR Abuse | -- | -- | Dintyala et al. (2020) | -- |
| FOR UPDATE Locking | innodb-locking | explicit-locking | -- | Schwartz et al. (2012) |
| Cartesian JOIN | -- | -- | Dintyala et al. (2020) | Karwin (2010) |
| Tool | Language | Approach | Comparison with QueryAudit |
| SQLCheck | C++ | Static SQL file analysis | QueryAudit analyzes queries at test runtime with index metadata |
| SAND | Java | Static analysis of source code | QueryAudit intercepts actual executed queries |
| datasource-proxy | Java | Query interception library | QueryAudit uses datasource-proxy as its interception layer |
| p6spy | Java | Query logging | Logging only; QueryAudit adds analysis and detection |
See Also