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.

SELECT ... LIMIT ROWS EXAMINED rows_limit;

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 than MAX_JOIN_SIZE rows.

SET @@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

See Also

• WAIT and NOWAIT
• Aborting statements that take longer than a certain time to execute
• lock_wait_timeout variable