Avoiding unnecessary optimizations and providing functionality fast, application developers can end up with code that works OK most of the times, but given a special case, can cause serious performance problems. Mine is the case of our CMS, and the way it handled hierarchical page list.

The content management system we've developed at my company has had some performance problems with large installations. The list of static pages the CMS manages took some seconds to load, and was a bit too unfriendly for the people using our software.

The Design

The starting point for my optimization was the a list of 350 pages in one language. That should not sound like a big number. 350 static pages is not much. However, the list took over two seconds to load, and we suspected something was not right. The load time was acceptable, but given a larger number of pages was worrying us. The site was on shared hosting, with about 50 other sites of various sizes.

Turning on query debugging, a feature of our CMS which was not available during the development of the pages list, revealed over 1400 database queries were made. The pages list logic was forgotten to us, but rereading the source code revealed the cultrip. The pages list code collected the root level pages, and then worked out if each page had subpages, collected them with another query, and then checked if they had subpages until no subpages were found and all pages had been checked for subpages. When this work was done, page privileges and translations were fetched in seperate queries, per page.

Thanks to the speed of modern hardware, this was still fast enough. We could have lived with that, and waited for a more severe case. However, as an excercise, I wanted to optimize the queries away.

The Optimization

The recursive style for querying the list was there because the CMS supports multiple database backends. None of which have recursive query support. And besides that, the User Interface code required the pages list to be a single flat list, sorted by the printing order of pages.

I started the optimization process by identifying the different data sets which were being pulled from the DB, and analyzing if the data could be collected in less queries. This was fairly simple task. I converted the logic of the page listing function to do only three queries: One for user privileges; one for translations; and one for the raw page data.

User privileges was a simple optimization. Instead of querying privileges for each page, I queried privileges for all the pages that would be queried soon with the page list query, but included only those pages for which the user had privileges.

The pages list was another easy case, since all it really needed was to get the list of pages with language matching the one the user was editing.

What proved problematic was the translations query. A pages translated versions were normalized in a seperate table with ony two attributes: a page identifier, and a group identifier. Pages with same group identifier were considered to be different versions of the same page in different languages.

This optimization could have been done with simply joining the tables, but that would have resulted in duplicated tuples. Not wanting to parse the results in code, I had to do some research to see if it was possible to return multiple results in one tuple. I already knew this was possible in PostgreSQL with the ARRAY and ARRAY_TO_STRING functions. But to make the code portable and usable in all our installations, it needed to support MySQL too. And there was no array functions in MySQL.

After googling, I found out that MySQL had a similar functionality but with a bit different query model. It was possible to turn multiple results into one string of text with GROUP_CONCAT function. The GROUP_CONCAT turns multiple results from a GROUP BY operation into one string of text, just like ARRAY_TO_STRING and ARRAY do for results of a subquery.

That solved my final problem with SQL. There were only three queries now, and the number of queries did not depend on the number of pages.

Sorting the results

Now that I had all the data in an unsorted list, I simply reimplemented the hierarchical approach of the old multi-query code to work with the single page list, and another utility table recording page-parent releations.

In the end, we had a simpler design, and more easily approachable code. I think this is a good example of optimization done right; code is optimized only when it's performance is becoming to be a problem.