Reaching the End of Summer with SQL Optimization
GSoC 2014 with the Project named SQL Optimization for Joomla CMS has come to an end which can be considered as a success full project according to my opinion. This project was not only otoptimize the queries but also to analyze the weaknesses and to find solution as well as research in new enhancements. After four months of hard work I came up with some solutions which can be identfied as the first step of a long journey of Joomla!'s database optimization. The vision of this project was to give my contribution to make Joomla! better than other Content Management Syetems and I hope I have given a contribution which will be a starting step of a long journey.
What is SQL Optimization Project for Joomla CMS
Joomla Being top most Content management system in the world we have as a community a huge competition from the other Content management systems. In most of the time what we consider about a CMS are the enhanced and the newest features of the CMS. But there is another important fact which we need to pay our attention as we consider the user perspectives. It is the speed of the system. This can depend on many factors. But the top most fact/bottleneck is the database management system and the way we use it store and retrieve data.
In my project of this year GSoC is mainly focused on the above mentioned fact. This project is named as SQL Optimization for Joomla CMS and as the name describes it focus on the existing SQL queries and their execution in order to identify the possible optimization methods for each of those queries. The vision of this project is to provide the community a faster way to interact with the CMS and to reduce the database management system based overheads.
Focused Areas for the Optimization
This project is based on the bellow focused areas of optimization,
- Categories related query optimization
- Articles related query optimization
- Tags, menus, languages related optimization
- Nested set optimization as an research area
The above mentioned research area was the last thing I focused and the other three areas were finished at first.
Carrying out Optimization Work
During the project work first thing I paid my attention was to have a proper understanding about the query execution procedure in Joomla Core and the fundamentals of SQL query optimization procedures. For this task I had to spend about 2-3 weeks at the beginning and then started working with the optimization work based on the research done on the optimization methods.
Basic Optimization Procedures followed
During this project bellow methods were followed for the optimization
- Introducing sub queries
- Introducing indexes
- Query decomposing
- Removing unnecessary calculations
At the end of the summer I could optimize many queries following the above procedures. Those were not only the queries executed in the user side, but also in the administrator side. In the administrator side the queries optimized were based on retrieving categories, articles, menus, tags. Also in the administrator side the main focus was displaying the categories, articles, tags, menus, adding new categories, tags, menu items, languages, etc. Locally I ran tests and after having a considerable amount of gain they were pushed to the main repository for the community testing. The amount of testing I did locally can be verified with the help of the community. After the verification and if we add these changes to the main repository I can guarantee the gain is significant. At the moment I have pushed all the changes to the main repository and the feed backs I get from the community is positive and I am very much happy to mention that fact here.
At the start of this project one of my milestones was improving the Joomla Debug plugin, but after the mid evaluation we decided to start a research on nested set optimization except the debug plugin enhancement. This is because the nested set optimization is one of the major focusing areas of the optimization. I finished the research work on the nested set optimization and there is remaining work to do in this area. I have taken the initial steps and with the help of the community we can carry on the started work.
Nested Set Optimization
In the nested set optimization work, my major focus was on allowing the component creators to design the database tables without having the path, level and parent_id fields. This is because these can be very easily calculated rather than storing them in the database and updating them every time which is very much costly. My approach was to have correlated sub queries in order to get those fields form the tables. For this purpose I created three methods at JTableNested class. When you extend the class from JTableNested these can be accessed. Those methods are as follows:
To get the path of a node by calculation:
To get the parent id of anode by calculation:
To get the level of a node by calculation:
Reference: nested.php access the methods
I have used getCorrelatedPathQuery ($mapField) to get the path by calculating for the #__categories, #__tags, #__menu tables. The queries regarding the above tables have been modified to work even if they don’t have the path field. You can test how these methods works by enabling the relevant Pull Request and then removing the path field form the above mentioned tables. This is a research area and need more and more testing to identify the loop holes regarding every possible situations.
How to Access my Project Work
All my project work resides in my GitHub repository. I will not include the code samples in this report because it will not be clear explaining here. All the details about the optimization work and the related test results for each and every query is well documented and the attached zip contains those documents. Although some of the PRs have been closed they have been merged in to other PRs and can be accessed there. Also I have listed down the links to the PRs in the main repository and I would like to kindly ask your help for testing them and sharing the results with us. All the testing instructions have been included with those PRs and feel free to propose your suggestions too.
We need your help
Honestly I would like to see the Joomla Community make use of my work. In order to make it a success we need more and more testing regarding the work. Therefore, I kindly invite all the Joomla Community members to help identify the weaknesses and the possible bugs in the work by testing the provided work.
Here I have attached the documents I created regarding each individual pull request I created for optimization with the testing results I got. Some of those pull requests have been merged together in order to send to the main repository. But the attached documents give a detailed description of those base PRs and the results.
Also if you are willing to test the PRs I would like to suggest this post to read in order to test them and bellow list consists the PRs which are sent to the main repository and I would recommend to follow the testing instructions describes there. Also If you would like to test each individual PR in my repository (GitHub) please refer the attached documents and also those documents provide my test results for the optimized queries.
This project is not a one which ends after the summer. This is the first step of a long journey of Joomla database Optimization. There are other SQL queries which we need to pay our attention rather than the optimized ones. Also the nested sets research work has more and more work to carry on after this initial step. Therefore I will be working on this project after the summer also to provide Joomla Community a valuable support to remain world's topmost position
Behind the success of this project there were two pillars who gave me the guidance and help whenever I needed it. They were Gunjan Patel and Nicola Galagano who supported me to achieve the expected outcome of this project. I am heartiestly thankful to both of them for helping me throughout the project. Also I would like to thank Mr. Ronni Christiansen (Managing director and Visionary at redCOMPONENET) who provided us hosting our test site for the project testing purposes throughout the project work. Also I would like to specially thank Eli Aschkenasy who gave me great support during this project work regarding the optimization techniques and how to identify the SQL query bottlenecks. Also I would really like to thank to all the Joomla community members who supported me from the beginning of this project and everyone who tested my optimized work willingly.
Finally I would like thank again and again everyone who was behind this project, because you all gave me the support to achieve this goal.