SQL Optimisation Working Group
From Joomla! Documentation
- Nadeeshaan Gunasinghe
- Gunjan Patel (Working Group Coordinator)
- Thomas Hunziker (PLT Contact: thomas.hunziker at joomla.org)
- Nicola Galgano
- Eli Aschkenasy
- Mrunal Pittalia
- Steven Pignataro
Goal of the Project
Joomla!, being one of the top content management systems in the world, is constantly faced with stiff competition. Often times what is considered important about any CMS are the enhancements and the feature sets a specific CMS has. There is another (sometimes overlooked) important fact which we need to pay attention to as we consider the end-user’s needs, which is the speed of the software. This depends on a multitude of factors but the top-most bottleneck is often the database management system and the way we use it to store and retrieve data.
In the SQL optimization working group we are mainly focused on the above mentioned fact. Our vision is to provide the community a faster way in which the CMS interacts with the DB by reducing the database management system based overheads.
You can see the foundation of our work group idea here, GSOC 2014 Project: SQL Optimization
- We have started this project as a Google Summer of Code SQL Optimisation Project https://www.google-melange.com/gsoc/project/details/google/gsoc2014/nadeeshaan/5657382461898752
- Sent Pull Request on Joomla-CMS which can be found here https://github.com/joomla/joomla-cms/pulls/nadeeshaan
The immediate areas we focused on were:
- Categories related query optimization
- Articles related query optimization
- Tags, menus, languages related optimization
- Nested set optimization as a research project
- Based on the above criteria we have submitted optimization work to the community and the current work can be seen at https://github.com/nadeeshaan/JGSoC-SQL-Optimisation . In the future we are planning to move this project to https://github.com/joomla-projects/GSoC-SQL-Optimization to ensure continuation of this work.
Roadmap & Future Plans
- We are focused to improve the Joomla database performance as well as the database access performance by efforts such as query refactoring and re-engineering. As our main target we are hoping to give a major priority for the schema redesign and also the nested sets optimization. At the moment we have laid the groundwork for the nested-set optimization but there is a lot more refactoring to be done.
- In addition we are planning to improve the Joomla! Debug Plugin to measure previous query time and time difference between old and new queries.
Following are the main areas that still need extensive work:
- Nested sets database schema redesign
- Debug Plugin improvement to measure query performance
- Find the rest of the huge queries and bottleneck of the software regarding performance
- Optimise queries of different views
Joomla! 4 SQL Optimization Plan
While we currently focus mainly on optimizing existing queries it is important to understand that we’re extremely limited due to backwards compatibility requirements. It is our hope that for Joomla! 4 the DB schema can be revisited and perhaps totally re-written. F0F has internally implemented various DB functions (specifically in the nested-sets area) that should be part of how core joomla tables are accessed.
You may read more about us on Joomla! Volunteers Portal - SQL Optimization Working Group