Search code examples
mysqlsqldatabase-performance

sql query optimisation on MySQL

I have the below SQL query and want to know if there is a way to optimise the query.

SELECT * FROM LogEntry WHERE UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(updateTime) > 10;

Will adding index on updateTime improve the performance on something more can be done to improve the performance

Solution

  • Yes. You need to think of a way to remove the function on the column. So if I have the logic right:

    SELECT le.*
FROM LogEntry le
WHERE le.updateTime < NOW() - interval 10 second;

    This can then take advantage of an index on LogEntry(updateTime).

    There are good reasons to explicitly list the columns being returned. This only has a small effect on performance -- unless the row size is quite big.