I've been analysing my website's performance using Google PageSpeed Insights, and the main issue it's reporting is slow server response times, which seem to average around 1.5 - 2 seconds. Google recommend trying to achieve 200ms, so I'm way off.
What I've established is that the queries to retrieve the data from the database are causing quite significant performance issues. My website makes extensive use of caching to retrieve the sections of the website. The entire webpage can usually be generated from 5 database queries from the cache table.
When I hard-code my return value to this (note the 'This is a test' selection):
$sql = "SELECT 'This is a test'
FROM cache
WHERE url = ?
AND language = ?
AND currency = ?";
$stmt = $conn->prepare($sql);
if (!$stmt) {
ThrowDBError($conn, $stmt, $sql);
}
$stmt->bind_param('sss', $url, $language, $currency);
if (!$stmt->execute()) {
ThrowDBError($conn, $stmt, $sql);
}
$stmt->bind_result($cache);
$stmt->fetch();
$stmt->close();
The server response speed drops to 0.5 seconds. When I put it back to select the actual data column page_body, like this:
$sql = "SELECT page_body
FROM cache_copy
WHERE url = ?
AND language = ?
AND currency = ?";
$stmt = $conn->prepare($sql);
if (!$stmt) {
ThrowDBError($conn, $stmt, $sql);
}
$stmt->bind_param('sss', $url, $language, $currency);
if (!$stmt->execute()) {
ThrowDBError($conn, $stmt, $sql);
}
$stmt->bind_result($cache);
$stmt->fetch();
$stmt->close();
The the server response time jumps consistently to 1.5 - 2 seconds for the 5 queries the website makes to the cache table.
The cache table only contains 8,000 rows and I think I've correctly indexed it. The column page_body is a mediumtext - does anyone know of performance-related issues using mediumtext? I just can't figure why it would add 1.5 seconds to the load time.
Here is the DDL for the cache table:
CREATE TABLE `cache` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`url` varchar(255) NOT NULL,
`language` varchar(2) NOT NULL,
`currency` varchar(3) NOT NULL,
`page_body` mediumtext NOT NULL,
`cached_time` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `url, language, currency` (`url`,`language`,`currency`) USING BTREE,
KEY `cached_time` (`cached_time`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Any ideas? Many thanks
