MySQL >= 5.7 allows a per query timeout as an optimizer hint. It only works for SELECT.

Here’s a 500ms timeout:

SELECT /*+ MAX_EXECUTION_TIME(500) */ COUNT(DISTINCT model.id)
FROM `model`

And in rails you can do

begin
  count = Model.all.optimizer_hints("MAX_EXECUTION_TIME(500)").count
rescue ActiveRecord::StatementTimeout
  puts "Uh-oH!"
end

It’s been easy to set a GLOBAL max query execution time in mysql for awhile. However this breaks mysqldump and other long running commands. post. I prefer an opt-in system where I can wrap only the problematic queries.

So I’ve been searching for a way to do better per-query timeouts and here it is!

MySQL docs