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?

Accepted Answer

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.

Low-priority query in Mysql

Solution

Problem