| Joomla SEF, SQL queries and speed |
|
Hello All Following a question on the forum, I thought I would explain a bit about the cache mechanism I have included in sh404SEF , in addition to the already existing one. And a bit of background on the matter as well. There is time penality in using a sef component VS using nothing or using Joomla standard SEF . Whenever a link to another page on the site needs to be displayed, like in the menus, or in latest news modules,... Joomla normally inserts a link like mysite.com/index.php?option=com_content&task=view&id=12&Itemid=3. If you ask for Joomla standard SEF, then Joomla simply changes that to something like mysite.com/content/view/12/3. As you can see, it is pretty much the same information, just displayed in another way, so that does not require any additional overhead, no additional request to the database, and it is as fast as can be Now if you install one of the SEF component, what they will do is replace id=12 (the article internal unique Id) by its title. To make the URL better, we may also want to add the article category, and possibly its section. You understand that to find out about an article title, category and section, the component has to make requests to the database. Something like 'Tell me what is the title of article number 12'. So if you have 2 or 3 requests (actually there are more if you factor in language, Itemid,...), for each URL on a page, then that ends up being a lot of added queries compared to standard Joomla SEF or no SEF.
With all menu links, latest news, footer, etc maybe you can have 30 or 40 links on a page, so that's an added 90 or 120 queries. Thats is very time consumming, especially if you are on a shared host, where the Mysql server is another machine than the Apache server. Here is an extract from a blog post at Alledia.com (read the whole article series on Joomla URL, it is very good):
Contrary to popular belief, most components actually cache the URL : once they have taken the pain of building it, querying article title and category, they store the 'finished' URL in the database, and next time they need it, it can be retrieved with just one query. At least JoomSEF and OpenSEF work this way, even though there are significant differences in the way they work internally. I have read that SEF Advanced only added this at last version, but of course because it it closed source, we don't know what exactly is meant by caching. There is also an advantage of doing this : these components also need to do the reverse : when a user click on a link, then Joomla is presented with an SEF URL, and it does not know about it. So the component must translate back to a non-sef URL (index.php?option=com_content&task=view.....) and pass that back to Joomla. If all SEF URL are nicely store in the database, it takes only one query to do the translate back job, and this is very fast. It is more the building up of each URL that takes time, rather than decoding it. Joomsef has made some good progress recently by optimising the way it builds the URL. OpenSef is supposedly fast, but I have read in their forum there may be issues when site has many URL because the reason OpenSEF makes few queries is that it pretty much loads all URLs in memory. So there is only one query, but a very time consuming one. What I have done in that field is add another layer of cache. When sh404SEF wants to find the SEF equivalent of an URL, before querying the database to find if it has already been calculated, it looks into the in-memory cache. This cache is a subset of the list of (SEF, non-SEF) couples stored in the database, but it is a simple php file, on the server disk.The thinking was :
I don't have real, timed figures on this, but it seems to be pretty OK, at least if you don't overflow your cache (the cache size can be set in the backend). When the cache is full, then additionnal URL are stored and retrieved from database, so we are back to what Joomsef does, yet with the benefits of 5 or 10 000 in the in-memory cache. As stated on extensions.siliana.net/en/sh404SEF-and-url-rewriting/ sh404SEFcomponent-for-Joomla-SEF-URL- rewriting.html , this mechanism allows the front page of this site to have all SEF URL, and yet only 3 additional queries to the DB compared to SEF switched off, including the fact that I am running Joomfish , which basically multiplies the number of queries by more than 2.
|
