Product Solutions Support
Try MemSQL

SLEEP

Halts execution of the thread for the given number of seconds.

Syntax

SLEEP ( seconds )

Arguments

Return type

Returns an integer. Specifically, SLEEP() returns the number 0 if it returns normally without interruption. If the query that is being halted is killed or times out, SLEEP() returns the number 1.

Examples

This function is useful mainly for debugging and measurement purposes, e.g., to make sure a list of queries runs at certain intervals.

memsql> select now(); select sleep(10); select now();
+---------------------+
| now()               |
+---------------------+
| 2015-03-02 23:22:25 |
+---------------------+

+-----------+
| sleep(10) |
+-----------+
|         0 |
+-----------+

+---------------------+
| now()               |
+---------------------+
| 2015-03-02 23:22:35 |
+---------------------+

This feature can be combined with views to enable row-level permissions. In this example, persons with the ‘employee’ role can only see themselves, while ‘managers’ can see all records:

memsql> create table employees (
    ->    id int primary key,
    ->    fullname varchar(64),
    ->    account_name varchar(64),
    ->    role enum('employee', 'manager'),
    ->    salary int
    -> );

memsql> create table permissions (
    ->    account_name varchar(64),
    ->    emp_id int,
    ->    primary key (account_name, emp_id)
    -> );

memsql> create view employee_view as
    ->    select e.* from employees e, permissions p
    ->    where e.id = p.emp_id
    ->    and p.account_name = current_user();

memsql> insert into employees values
    -> (1, 'SYSTEM', 'root@%', 'manager', 0),
    -> (2, 'Kevin Kelvin', 'kelvin@%', 'manager', 5000),
    -> (3, 'Oliver Heavyside', 'oliver@%', 'employee', 3000);

memsql> insert into permissions values
    -> ('root@%', 1), ('root@%', 2), ('root@%', 3),
    -> ('kelvin@%', 2), ('kelvin@%', 3),
    -> ('oliver@%', 3);

-- as root:
memsql> select current_user(), * from employee_view;
+----------------+----+------------------+--------------+----------+--------+
| current_user() | id | fullname         | account_name | role     | salary |
+----------------+----+------------------+--------------+----------+--------+
| root@%         |  1 | SYSTEM           | root@%       | manager  |      0 |
| root@%         |  2 | Kevin Kelvin     | kevin@%      | manager  |   5000 |
| root@%         |  3 | Oliver Heavyside | oliver@%     | employee |   3000 |
+----------------+----+------------------+--------------+----------+--------+

-- as kelvin:
mysql> select current_user(), * from employee_view;
+----------------+----+------------------+--------------+----------+--------+
| current_user() | id | fullname         | account_name | role     | salary |
+----------------+----+------------------+--------------+----------+--------+
| kelvin@%       |  2 | Kevin Kelvin     | kevin@%      | manager  |   5000 |
| kelvin@%       |  3 | Oliver Heavyside | oliver@%     | employee |   3000 |
+----------------+----+------------------+--------------+----------+--------+

-- as oliver:
mysql> select current_user(), * from employee_view;
+----------------+----+------------------+--------------+----------+--------+
| current_user() | id | fullname         | account_name | role     | salary |
+----------------+----+------------------+--------------+----------+--------+
| oliver@%       |  3 | Oliver Heavyside | oliver@%     | employee |   3000 |
+----------------+----+------------------+--------------+----------+--------+
Was this article useful?