Low-priority query in Mysql

Question

Is it possible to assign a lower priority to a query in MySQL similar to the nice command on the command-line (in Linux)? If not, are there databases that can do something like that?

Answer 1

You can use the LOW_PRIORITY or HIGH_PRIORITY in your queries depending on the type of query you execute:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] INTO ...
SELECT [HIGH_PRIORITY] * FROM ...
UPDATE [LOW_PRIORITY] table ...

From the Mysql 5.7 documentation for the INSERT query for instance:

If you use the LOW_PRIORITY keyword, execution of the INSERT is delayed until no other clients are reading from the table. This includes other clients that began reading while existing clients are reading, and while the INSERT LOW_PRIORITY statement is waiting. It is possible, therefore, for a client that issues an INSERT LOW_PRIORITY statement to wait for a very long time.

But it is also said:

This affects only storage engines that use only table-level locking (such as MyISAM, MEMORY, and MERGE)

So you won't be able to use this functionnality with innodb for instance, unless you want to use LOCK_TABLES and thus reduce its performance.