Antonio Badia
ABSTRACT
MySQL is an OLTP system without the ability to handle SQL subqueries. As part of our project in query optimization, we add subquery processing to MySQL through rewriting. First, we incorporate the ability to handle subqueries in the FROM clause; then rewrite queries with subqueries in the WHERE clause by moving the subquery to the FROM clause and introducing suitable conditions to link the subquery and the main query tables. The result is a complete and correct unnesting procedure for SQL queries. The idiosyncrasies of SQL semantics make some rewritings tricky. While optimization issues are not directly addressed, the rewriting opens up the possibility of more efficient query processing in several cases.
- Chanda, M. and Badia, A. Adding Subqueries to MySQL Through Query Rewriting, Technical Report 02-01. Available at http://date.spd.louisville.edu/forloop.Google Scholar
- Chaudhuri, S. and Dayal, U., An Overview of Data Warehousing and OLAP Technology, ACM SIGMOD Record 26(1), March 1997. Google ScholarDigital Library
- Dayal, U. Of Nests and Trees: A Unified Approach to Processing Queries That Contain Nested Subqueries, Aggregates, andGoogle Scholar
- Galindo-Legaria, C. and Rosenthal, A. Outerjoin Simplification and Reordering for Query Optimization, ACM TODS, 22(1), 1997. Google ScholarDigital Library
- Ganski, R. and Wong, H. Optimization of Nested SQL Queries Revisited, in Proceedings of the 1987 ACM SIGMOD Conference. Google ScholarDigital Library
- Gray, J., Bosworth, A., Layman, A. and Pirahesh, H. DataCube: A Relational Aggregation Operator Generalizing Group By, Cross-Tab, and Sub-Totals. In Proceedings of the 12th ICDE Conference, 1996. Google ScholarDigital Library
- Goel, P. and Iyer, B. SQL Query Optimization: Reordering for a General Class of Queries, in Proceedings of the 1996 ACM SIGMOD Conference. Google ScholarDigital Library
- Jarke, M., Lenzerini, M., Vassiliou, Y. and Vassiliadis, P. Fundamentals of Data Warehousing, Springer-Verlag, 2000. Google ScholarDigital Library
- R. Kimball, Why Decision Support Fails and how to fix it, SIGMOD Record, 24(3), 1995. Google ScholarDigital Library
- W. Kim, On Optimizing an SQL-Like Nested Query, ACM TODS, 7(3), 1982. Google ScholarDigital Library
- Muralikrishna, M. Improving Unnesting Algorithms for Join Aggregate Queries in SQL, in Proceedings of the 18th VLDB Conference, 1992. Google ScholarDigital Library
- MYSQL, http://www.mysql.com.Google Scholar
Index Terms
- Adding subqueries to MySQL, what does it take to have a decision-support engine?
Recommendations
Execution strategies for SQL subqueries
SIGMOD '07: Proceedings of the 2007 ACM SIGMOD international conference on Management of dataOptimizing SQL subqueries has been an active area in database research and the database industry throughout the last decades. Previous work has already identified some approaches to efficiently execute relational subqueries. For satisfactory performance,...
Optimizing complex queries based on similarities of subqueries
As database technology is applied to more and more application domains, user queries are becoming increasingly complex (e.g. involving a large number of joins and a complex query structure). Query optimizers in existing database management systems (DBMS)...
Equivalence and minimization of conjunctive queries under combined semantics
ICDT '12: Proceedings of the 15th International Conference on Database TheoryThe problems of query containment, equivalence, and minimization are fundamental problems in the context of query processing and optimization. In their classic work [2] published in 1977, Chandra and Merlin solved the three problems for the language of ...
Comments