Ramywhite Ramywhite - 1 month ago 12
JSON Question

select statement runs very slow on mysql

This php select statement runs very slow even with limit = 10.

$conn = new mysqli($DBServer, $DBUser, $DBPass, $DBName);

if ($conn->connect_error) {
trigger_error('Database connection failed: ' . $conn->connect_error, E_USER_ERROR);
exit();
}

$sql = 'SELECT id, date, UNIX_TIMESTAMP(date) AS UDATE, col1, col2 FROM ' . $table . ' ORDER BY UDATE DESC LIMIT ' . $id . ', ' . $limit . ';';

$result = $conn->query($sql);
$data = array();
if($result === false) {
trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR);
}
else {
while ($row = $result->fetch_assoc()) {
$data[] = $row;
}
}

$result->free();

echo json_encode(array_reverse($data));


Also the table has very large data 14 millions rows and it is MyISAM engine.

How could I optimize this script to run faster?

Here's my CREATE TABLE

CREATE TABLE `my_tbl`
(`id` int(11) NOT NULL AUTO_INCREMENT,
`date` varchar(45) NOT NULL,
`col1` float NOT NULL,
`col2` float NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `date` (`date`))
ENGINE=MyISAM AUTO_INCREMENT=14475739
DEFAULT CHARSET=latin1


Many thanks

Answer

There's a number of issues in this query, but the biggest of those is that you're calling ORDER BY on a computed column. This requires scanning the entire table to produce that derivative value, then ordering the results, something that often involves creating a temporary table on disk. It's brutally slow for large amounts of data.

Since the value you're sorting is a date, and since SQL has a DATE type which is ideal for storing these, ensure date is of type DATE and add an index on that column to handle the ordering.

Ideally you switch from MyISAM to InnoDB, which can be tuned better, and take the opportunity to rename the column to something other than date which is a reserved keyword.