Source Code Optimization
The source code of the application that will access the MySQL server must be optimized to use the least amount of resources of the MySQL server as possible. Even if you are not a software developer, you should read this section, as there is one option that we will discuss (caching on the web server) that your software may support and it may not be enabled.
The MySQL caching mechanism is case-sensitive. This means that the queries “SELECT id,name FROM table” and “select id,name from table” are treated differently by the caching mechanism, even though both generate the same results. So, if the first query is cached when you run the second query, the MySQL server won’t use the query that is already cached; it will run the query again, which is not desirable. Therefore, the best practice is to standardize the way MySQL queries are written within the application source code, so the application can better use MySQL’s caching mechanism.
On SELECT statements, only select the rows you are really going to use, as this will save resources on the MySQL server. “SELECT *” statements are a big no-no.
Ideally, JOIN statements must use indexes.
When coding, try to use as few separate MySQL queries as possible; whenever possible combine MySQL queries.
Whenever possible, use a caching mechanism on the web server, so you don’t need to load from the MySQL server frequently-accessed data that doesn’t change very often. This caching must be done preferably in RAM, so when the web server needs data that is already cached, the data can be accessed almost instantly, meaning a faster load time for the page that the server must present.
Some smart software developers already added built-in support for caching mechanisms, such as Xcache, but this support is disabled by default, since the developer can’t know for sure if the web server where the software will be installed has the caching mechanism enabled or not.
Therefore, read the documentation of the software you are using on your web server to see if it supports any caching mechanism, and what to do to enable it. For example, on vBulletin, a popular forums software, you must uncomment the following line from its config.php file in order for it to use Xcache to store frequently-accessed data in RAM:
$config[‘Datastore’][‘class’] = ‘vB_Datastore_XCache’;
In this example, you need to comment out all other $config[‘Datastore’][‘class’] lines that may exist in config.php file.
Of course, you must have Xcache installed on your webserver and its “var_cache” correctly enabled in php.ini.
Even if your software doesn’t support a caching mechanism, we highly recommend you install Xcache on your web server, as it caches and optimizes PHP code, increasing the performance of your web server and lowering its CPU load.
The exact steps for installing Xcache are outside the scope of the present tutorial.