How to Abort SQL statements after a set time in MariaDB

0 min read 137 words

Sometimes you don’t want a SELECT query to run for more than a set amount of time.

This is a roundabout way to make your server doesn’t die from slow-running queries.

Obviously, you should tweak your database and potentially run your SELECT queries through an EXPLAIN plan first. This allows you to create appropriate indexes and find why things are slow in the first place.

But: sometimes you still find yourself wanting to kill queries that may run over a certain amount of time.

How to abort slow queries

Your original query:

SELECT b.domain as domain ...

Your new query:

SET STATEMENT max_statement_time=30 FOR SELECT b.domain as domain ...

Notice the addition of the SET STATEMENT max_statement_time=30 right before the SELECT.

This is measured in seconds, so the above query will automatically be killed after 30 seconds.

Tags:
Andrew
Andrew

Andrew is a visionary software engineer and DevOps expert with a proven track record of delivering cutting-edge solutions that drive innovation at Ataiva.com. As a leader on numerous high-profile projects, Andrew brings his exceptional technical expertise and collaborative leadership skills to the table, fostering a culture of agility and excellence within the team. With a passion for architecting scalable systems, automating workflows, and empowering teams, Andrew is a sought-after authority in the field of software development and DevOps.

Tags

Recent Posts