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
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.
updateTime) inside a function (UNIX_TIMESTAMP and -)" is an important rule is optimization.The first thing i would do is remove your select * and select specific columns
In addition to the optimisation of the request, you may think about partitioning your table. For instance, you could partition the table LogEntry on updateTime by day. It would speed your request drastically if you have a lot of data in your table.
Note: to partition your table on updateTime, updateTime must be part of the primary key.
unixTime inside that expression. So, it cannot do any pruning.
SELECT ... WHERE x=1 AND date BETWEEN... works excellently with INDEX(x,date). Adding partitioning will not speed it up; it would then need to do "partition pruning" before using the index. Do you have an example for me to critique?