UC Irvine

Query performance is critical in database-supported applications where users need answers quickly to make timely decisions. For decades, databases have relied heavily on query rewriting to optimize SQL query performance. However, with the current prevalent use of business intelligence and interactive visualization systems, purely relying on the rewriting capabilities inside databases is insufficient to optimize queries generated by those modern applications. On the one hand, different applications have various performance requirements. Some applications prioritize responsiveness, requiring queries to be executed within strict time constraints, and others may prioritize accuracy. Traditional database-centric approaches fail to exploit such information and adapt to diverse application requirements. On the other hand, developers and domain experts possess valuable insights into the data and query patterns specific to their applications. However, the query optimization techniques customized using domain knowledge can be infeasible inside databases.In this thesis, we focus on providing middleware-based query-rewriting techniques to help databases seize opportunities to optimize queries. First, for many applications with stringent response time constraints, such as interactive visualization systems, we propose a machine learning-powered query rewriting framework (called Maliva) to rewrite the queries with various options and help the databases generate efficient plans. Maliva leverages those expensive and high-accuracy query cost estimators to guide their rewriting process. By considering a pre-defined time constraint, Maliva judiciously explores different rewriting options and balances the query planning time and the execution time to find an efficient rewritten query that meets the time constraint. Second, in many cases, developers want to use their domain knowledge about the applications and datasets to rewrite queries for better performance. We propose a human-centered query rewriting solution (called QueryBooster) to provide users with an express and easy-to-use rule language to define rewriting rules. In addition, QueryBooster allows users to express their rewriting intentions by providing example query pairs. QueryBooster then automatically generalizes them into rewriting rules and suggests high-quality ones to the users. Finally, to lower the bar of users adopting the proposed rewriting framework, we implemented QueryBooster as middleware-based multi-user system to provide query rewriting between applications and databases as a service. Treating both the applications and databases as black boxes, QueryBooster requires no code modifications to them. To use the service, users only need to replace the database connector between the application and the database with a customized version provided by QueryBooster. The customized connector automatically intercepts application queries and sends them to QueryBooster to rewrite them based on user-defined rewriting rules. QueryBooster’s service model brings SQL query rewriting to a new paradigm where (1) users can easily formulate, control, and monitor query rewriting; (2) they can share rewriting knowledge and benefit from the wisdom of the crowd; and (3) they enjoy the non-intrusiveness security and pay-as-you-go convenience.