Query Limits and Timeouts

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

LIMIT

SELECT ... LIMIT row_count
OR
SELECT ... LIMIT OFFSET, row_count
OR
SELECT ... LIMIT row_count OFFSET OFFSET

The LIMIT clause restricts the number of returned rows.

LIMIT ROWS EXAMINED

SELECT ... LIMIT ROWS EXAMINED rows_limit;

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

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;

max_join_size

If the max_join_size variable (also called sql_max_join_size) is set, then it will limit any SELECT statements that probably need to examine more thanMAX_JOIN_SIZE rows.

SET @@MAX_JOIN_SIZE=1000;
->ERROR 1104 (42000): The SELECT would examine more than MAX_JOIN_SIZE ROWS; 
SELECT COUNT(null_column) FROM big_table;
  CHECK your WHERE AND USE SET SQL_BIG_SELECTS=1 OR SET MAX_JOIN_SIZE=# IF the SELECT IS okay

max_statement_time

If the max_statement_time variable is set, any query (excluding stored procedures) taking longer than the value of max_statement_time (specified in seconds) to execute will be aborted. This can be set globally, by session, as well as per user and per query. See Aborting statements that take longer than a certain time to execute.

See Also

• WAIT and NOWAIT

• Aborting statements that take longer than a certain time to execute

• lock_wait_timeout variable

_{This page is licensed: CC BY-SA / Gnu FDL}