Leveraging Application Data Constraints to Optimize Database-Backed Web Applications

Exploiting the relationships among data, such as primary and foreign keys, is a classical query optimization technique. As persistent data is increasingly being created and maintained programmatically (e.g., web applications), prior work that focuses on inferring data relationships by tabulating statistics from the stored data misses an important opportunity. We present ConstrOpt, the first tool that identifies data relationships by analyzing the programs that generate and maintain the persistent data. Once identified, ConstrOpt leverages the found constraints to optimize the application's physical design and query execution by rewriting queries. Instead of developing a fixed set of predefined rewriting rules, ConstrOpt employs an enumerate-test-verify technique to automatically exploit the discovered data constraints to improve query execution. Each resulting rewrite is provably semantically equivalent to the original query. Using 14 real-world web applications, our experiments show that ConstrOpt can discover over 4306 data constraints by analyzing application source code. On 3 of the evaluated applications, among queries with at least one constrained column, 42% can benefit from data layout optimization, and 35% are optimized by changing the application code. Finally, ConstrOpt's constraint-driven optimizer improves the performance of 826 queries, 9.8% of which has over 2x speedup.