select statement runs very slow on mysql

Question

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

Have you tried not using the horrible MyISAM engine? InnoDB should be your default, and it's much easier to tune. This will be brutally slow because you're calling `ORDER BY` on a computed column. Order by the unmodified date. — tadman, Nov 01 '16 at 16:14
**WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST` or `$_GET` data directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman, Nov 01 '16 at 16:16
I have edited my question. I have a primary key for 'id' column and UNIQUE key for 'date' column, should I make more indexes? — Ramywhite, Nov 01 '16 at 16:25
@tadman 'date' column is varchar, so if I use it for ORDER BY without UNIX_TIMESTAMP(date), that will give me the same results? — Ramywhite, Nov 01 '16 at 16:32
There's your problem. Set your `date` column to be of type `DATE` and add an index to it. That's also a chance to give it a name that doesn't conflict with the MySQL keyword of the same name. Ordering on an indexed column is effortless for the database. — tadman, Nov 01 '16 at 16:40
@Ramywhite If you want this question to be reopened, then you need to tell the person below that what they posted is already something you tried and will need to adjust their answer in consequence. You can ping me to let me know you have and I will be glad to reopen the question in order for others to offer other possible solutions, if the link from the duplicate/answer given did not solve the question. — Funk Forty Niner, Nov 01 '16 at 16:57
(addendum to the above) Although seeing a comment by @tadman - the solution seems most likely to be in the duplicate question. — Funk Forty Niner, Nov 01 '16 at 17:02
@tadman Converting date column to datetime type, removing UNIX_TIMESTAMP(date) and changing the engine to innodb, solve the problem, it is super-fast now, if you combine your comments into one answer I will accept it. Many thanks! — Ramywhite, Nov 01 '16 at 18:12

score 2 · Accepted Answer · answered Nov 02 '16 at 00:30

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.

select statement runs very slow on mysql

1 Answers1