zaterdag 7 juni 2014

What is a "slow query"?

A slow query is a query that takes longer than you'd like it to take.

Note the words "longer than you'd like". There is no hard limit, no number of milliseconds that define how long "a query" should take. If it takes 6.2 seconds, then that's how long it takes and that's NOT a problem untill you have a need to do it in less than 6.2 seconds.

I have been in serious discussions (including rude words) with hosters who thought they could just pick '1 second' as a maximum runtime for a query, and kill it if it went over that limit. Their reasoning was literally that queries don't take more than a few milliseconds, so anything over  second must be a serious problem. This stems entirely from MySQL's history of not being able to cope with complex queries, and the subsequent progammingstyle of using lots and lots of very very small queries. Small queries complete quickly, and I you have no experience in working wth databases it's logical to assume that sub-second queries are the way to go.

This is of course complete and utter <rude word>

If you need to update 1.5mln rows there is simply no way that that is going to complete in less than a second, and if you run 1.5mln separate update queries that each take 2ms then the hole operation is going to take 300 seconds, which is many times as long as the single update query.

The only case where a query can take "too long" is when *you* need the task to be completed quicker.

There is no such thing as "too long", there are only "too long for your requirements" and "longer than it needs to".