SQL performance in Docman
I have mentioned before that I am working with Joomla and Docman for this FTA site and I recently found a bit of a scaling issue I thought I would mention. I don’t know how many of you use the query log option that MySQL offers, but I highly recommend you do.
I browsed to the resources section and viewed a page with 124 documents on it. You would think that wouldn’t be so bad, but in actuality it is ridiculous. That one page view generated 1611 SQL queries. The reason is that it grabs the list of documents, and then for each one it would check to see if the user had access to that category and there was another check that has slipped my mind. Just the category access check was for some reason causing 10 SQL queries per document and what makes it worse is that every document was in the same category so really only one access check needed to be done. Since all Docman categories are 100% open to the public I had no problem removing the SQL query and having it return TRUE for every document. This change dropped the queries from 1200 down to 155. The reason it is 1200 and not 1600 is because the other check that I still can’t remember was also removed and that eliminated roughly 300 queries.
Personally I think that the component could have handled grabbing the information better than it was; joins being the biggest improvement.
