Real life example:
SELECT username FROM users WHERE (ADDDATE( last_in_chat, INTERVAL 2 MINUTE) > NOW()) ORDER BY username LIMIT 30

NEVER do that. The user in question runs this query on table with 3000+ rows, which means, that SQL server (MySQL in
this case) is doing 3000+ date/time additions and will never use any index. If you write the query this way:

SELECT username FROM users WHERE last_in_chat> NOW()-interval 2 minute ORDER BY username LIMIT 30

the server will do only one date/time substraction and will probably use index if there is such on last_in_chat. The second query runs much faster, even without index. In this case not having index on last_in_chat maybe is a better option as this field probably updates on user actions in chat room, which may happen more ofthen than the displaing of this list.

Mind the Query cache.

MySQL have a nice feature - query cache, which stores query results and if you rerun your query, you will have your result served from the cache, which is quite faster. The cache expires at the first update/insert/delete query on the table (or any of the tables in case of join), so you will not get outdated results. The downside is that when you use functions like NOW(), CURRENT_DATE(), UNIXTIME() and so on, the server will not put your query in the cache, as next time the query will be executed this functions may return different value.

You can rewrite the query from the example above to use the query cache:
SELECT username FROM users WHERE last_in_chat>'2005-09-15 16:44:00' ORDER BY username LIMIT 30

where “2005-09-15 16:44:00″ is computed in the programming language, from which you run query like this (PHP):

$time=date("Y-m-d H:i:00", time()-120);

As you see, the seconds are hardcoded to be 00. This sacrifices correctness of the query and now you will see users which was last in chat 2 to 3 minutes. You shoud decide if you care about this. In this case this may be not a good option, but if you want users, that was active in last 15 minutes, you will most probably will not care about 1 minute.

so when you run this query, it will be stored in the cache and the cached result will be used during this minute as all the queries will be the same and will not have one of “bad” functions in it. On the next minute, query will be:

SELECT username FROM users WHERE last_in_chat>'2005-09-15 16:45:00' ORDER BY username LIMIT 30

and this will generate new results.

Of course you shoud use this trick only if you think that this query will be run more then once per minute, but if not you maybe don’t need any performance tips.

This trick is better illustrated when you substitute CURRENT_DATE() with current date, generated from your program as this changes quite less often than NOW() (once in 24 hours actually) and you can have a query served from the cache for a whole day.