I have a table which stores product reviews for a website. The table uses varchar(1000) to store the review comment average response time is 0.5 seconds. I changed the datatype of the column that holds data to mediumtext and the page response time jumps to 1.5 - 2 seconds. Baring in mind no additional data was added to the column and the PHP code is the same.
I don't think the query time is the issue, as MySQL reports it takes 0.019secs, which is the same whether varchar or mediumtext.
I'm at a loss to understand what's happened here. I'm using MySQL PDO and PHP. I don't know if it's a server issue, an inherent issue with mediumtext, or something else entirely.
I tried changing the table type from MyISAM to InnoDB but it made no difference.
Here is the PHP code:
$charset = 'utf8mb4';
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$attrs = array(PDO::ATTR_PERSISTENT => true);
$pdo = new PDO($dsn, $user, $pass, $attrs);
$stmt = $pdo->prepare("SELECT SQL_NO_CACHE comment
FROM reviews_product_comments
WHERE part_id =:partid
and language =:language");
foreach ($parts as $part) {
// bind the parameters
$stmt->bindValue(":partid", $part);
$stmt->bindValue(":language", "en");
if ($stmt->execute()) {
if ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$comment = $row['comment'];
echo $comment . "<br>";
}
}
}
$pdo = null;
Any ideas?
The two datatypes are handled virtually identically. There are many other possible reasons for sluggishness. (But none that I know of that would say
MEDIUMTEXT
is worse thanVARHAR
.)So... Let's see if we can speed up the web page...
Put
microtime(true)
around the mysql calls -- to make sure it is MySQL, not PHP. "0.019secs" makes sense; "1.5 - 2 seconds" sounds like something is going on in PHP.Use InnoDB, not MyISAM. (In spite of your claims to the contrary.)
Tune correctly; let's see
SHOW VARIABLES LIKE '%buffer%';
How much RAM do you have? (Swapping is terrible for performance.)How many rows are you returning? It's not practical to have more than a few dozen on a web page, so add
ORDER BY...LIMIT...
.If the UI limit is 1000 characters, use
TEXT
orVARCHAR(1000)
, notMEDIUMTEXT
. If you are trying to raise up to 64K bytes (potentially 4K utf8mb4 characters), then useTEXT
.You do need this (with the columns in either order):
If there has been a lot of "churn" (deletes and/or updates followed by more inserts) in the MyISAM table, the data can be fragmented, hence slow. This can happen for both
VARCHAR
andTEXT
. This does not happen with InnoDB.