Who is Online / User Info Block SQL Performance
Date: Tuesday, August 26 @ 23:55:00 CEST
Topic: Bug Fixes


If you are using the WHO IS ONLINE / User Info Block on your website, you should monitor the Server Load, because this block contains a very bad SQL Select string.

In order to Display the Members who have registered Yesterday the SQL Select consist of a WHERE clause containing LIKE '%' Wildcards to replace the space characters in the date string.

If you take the SELECT and run in mysql the EXPLAIN command, you will see that this query can NOT use any index. This is because of the WILDCARD. You should replace the '%' wildcards with real SPACES (you have to use String Length Functions to adjust the number of spaces dependeng if the day is one or two digits) in order to use the INDEX. This speeds up the query on big user tables with a factor of at least 10x....!!!

You find a modified version at http://www.comicfan.de/modules.php?name=Downloads&d_op=getit&lid=59





This article comes from NukeCops
http://www.nukecops.com

The URL for this story is:
http://www.nukecops.com/modules.php?name=News&file=article&sid=574