The Ultimate Guide to High Availability with MariaDB

How to limit/timeout queries

You are viewing an old version of this article. View the current version here.

This article describes the different methods that MariaDB provides to limit/timeout a query:

Contents

  1. LIMIT
  2. LIMIT ROWS EXAMINED
  3. SQL_SAFE_UPDATES
    1. SQL_SELECT_LIMIT
    2. SQL_MAX_JOIN_SIZE
    3. MAX_QUERY_TIME variable

LIMIT

SELECT ... LIMIT row_count
or
SELECT ... LIMIT offset, row_count
or
SELECT ... LIMIT row_count OFFSET offset

The LIMIT clause restrict the number of returned rows.

LIMIT ROWS EXAMINED

SELECT ... LIMIT ROWS EXAMINED rows_limit;

Stops the query after 'rows_limit' number of rows has been examined.

This is feature appeared in MariaDB 10.0.0

SQL_SAFE_UPDATES

If the SQL_SAFE_UPDATES variable is set, one can't execute an UPDATE or DELETE statement unless one specifies a key constraint in the WHERE clause or provide a LIMIT clause (or both).

SET @@SQL_SAFE_UPDATES=1
UPDATE tbl_name SET not_key_column=val;
-> ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

SQL_SELECT_LIMIT

SQL_SELECT_LIMIT acts as an automatic LIMIT row_count to any SELECT query.

SET @@SQL_SELECT_LIMIT=1000
SELECT * from big_table;

The above is the same as:

SELECT * from big_table LIMIT 1000;

SQL_MAX_JOIN_SIZE

If the SQL_MAX_JOIN_SIZE is set, then it will limit any SELECT statements that probably need to examine more than SQL_MAX_JOIN_SIZE rows.

SET @@SQL_MAX_JOIN_SIZE=1000;
SELECT count(null_column) from big_table;
->ERROR 1104 (42000): The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

MAX_QUERY_TIME variable

You can limit the time any query is executed by setting the MAX_QUERY_TIME variable. The variable takes the number of seconds (with microsecond precession) the query should be executed. If the query is run more than the given timeout, it will be killed as by a KILL command.

SET @@MAX_QUERY_TIME=0.5; # Abort if query takes more than 1/2 second
SELECT SLEEP(100);

max_query_time can also be given as an argument to mysqld at startup.

This feature is scheduled for MariaDB 5.5.29

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.