Abstract
Query Optimization is an important process in Relational databases. Here, we present an overview of the Query Optimization process, with a focus on providing insight of MySQL sub-queries to optimize Join Operation. Efficient join processing for sub queries is one of the most fundamental and well-studied tasks in database research. For a given query, there are many plans that can be considered, though the output will be the same, but amount of time required is a key consideration. In this work, we examined existing algorithms for the way the sub queries are joined over many relations and described a novel sorting based algorithm to process these queries optimally. The algorithm is implemented and the results are compared with current strategies of MySQL. The proposed sorting based algorithm outperforms current algorithms without applying DBMS’ advanced techniques like parallel processing, hashing etc.
This is a preview of subscription content, log in via an institution to check access.
References
Connolly, T., Begg, C.: Database Systems: A Practical Approach to Design, Implementation and Management, 3rd edn. Wesley (2011)
Johnson, J.L.: Database: Models, Languages, Design. Oxford University Press (1997)
Ramakrishnan, R., Gehrke, J.: Database Management Systems, 3rd edn. McGraw Hill (2000)
Graefe, G.: Query evaluation techniques for large databases. ACM Comput. Surv. 25(2) (1993)
Ioannidis, Y.: Query Optimization. The Computer Science and Engineering Handbook, pp. 1038–1057 (1997)‎
Giving optimization hints to DB2, IBM, 2003. Available from http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.admin/p9li375.html
Bruno, N., Chaudhuri, S., Ramamurthy, R.: Power hints for query optimization. In: Proceedings of the International Conference on Data Engineering (ICDE) (2009)
Pund, M.A., Jadhao, S.R., Thakare, P.D.: A role of query optimization in relational database. Int. J. Sci. Eng. Res. 2(1) (2011)
Melton, J., Simon, A.: Understanding The New SQL: A Complete Guide. Morgan Kaufman
Graefe, G., Dewitt, D.J.: The exodus optimizer generator. In: Proceedings of ACM SIGMOD, San Francisco (1987)
Sudarshan, S., Korth, H.F.: Database System Concepts, 5th edn
Sassi, M., Grissa-Touzi, A.: Contribution to the query optimization in the object-oriented databases. Proc. World Acad. Sci. Eng. Technol. 6, pp. June 2005 1307-6884
Ioannidis, Y., Kang, Y.C.: Randomized Algorithms for Optimizing Large Join Queries, March 2013
Selinger, P.G., Astrahan, M.M., Chamberlin, D.D., Lorie, R.A., Price T.G.: Access path selection in a relational database system. In: Readings in Database Systems
Kofler, M.: MySQL 5, 3rd edn
MySQL Reference Manual, [Online]. Available from http://dev.mysql.com/doc/
Ioannidis, Y.E.: Left-deep versus bushy trees: an analysis of strategy spaces and its implications for query optimization. In: ACM SIGMOD International Conference on Management of Data, pp. 168–177 (1991)
Chimenti, D., Gamboa, R., Krishnamurthy, R.: Towards an Open Architecture for LDL. In: Proceedings of VLDB, Amsterdam (1989)
Heller Stein, J.M.: Predicate migration placement. In: Proceedings of ACM SIGMOD, March 1994
Banubakode, A., Haridasa, A.: Adv. Comput. Sci. Technol. 4(1), 83 (2011)
Christodoulakis, S.: Implications of certain assumptions in database performance evaluation. ACM TODS 9(2) (1984)
Author information
Authors and Affiliations
Corresponding author
Editor information
Editors and Affiliations
Rights and permissions
Copyright information
© 2018 Springer Nature Singapore Pte Ltd.
About this paper
Cite this paper
Shah, B., Pareek, J., Kanziya, D. (2018). A Novel Approach to Optimize Subqueries for Open Source Databases. In: Yang, XS., Nagar, A., Joshi, A. (eds) Smart Trends in Systems, Security and Sustainability. Lecture Notes in Networks and Systems, vol 18. Springer, Singapore. https://doi.org/10.1007/978-981-10-6916-1_31
Download citation
DOI: https://doi.org/10.1007/978-981-10-6916-1_31
Published:
Publisher Name: Springer, Singapore
Print ISBN: 978-981-10-6915-4
Online ISBN: 978-981-10-6916-1
eBook Packages: EngineeringEngineering (R0)