Deleting documents

Deleting is only supported for:

  • real-time indexes,
  • percolate indexes
  • distributed indexes that contain only RT indexes as agents

You can delete existing rows (documents) from an existing index based on ID or conditions.

Deleting documents is supported via SQL and HTTP interfaces.

SQL response for successful operation will show the number of rows deleted.

json/delete is an HTTP endpoint for deleting. The server will respond with a JSON object stating if the operation was successful or not and the number of rows deleted.

To delete all documents from an index it's recommended to use instead the index truncation as it's a much faster operation.

📋
DELETE FROM index WHERE where_condition
  • index is a name of the index from which the row should be deleted.
  • where_condition for SQL has the same syntax as in the SELECT statement.

In this example we are deleting all documents that match full-text query dummy from index named test:

📋
select * from test;

delete from test where match ('dummy');

select * from test;
Response
+------+------+-------------+------+
| id   | gid  | mva1        | mva2 |
+------+------+-------------+------+
|  100 | 1000 | 100,201     | 100  |
|  101 | 1001 | 101,202     | 101  |
|  102 | 1002 | 102,203     | 102  |
|  103 | 1003 | 103,204     | 103  |
|  104 | 1004 | 104,204,205 | 104  |
|  105 | 1005 | 105,206     | 105  |
|  106 | 1006 | 106,207     | 106  |
|  107 | 1007 | 107,208     | 107  |
+------+------+-------------+------+
8 rows in set (0.00 sec)

Query OK, 2 rows affected (0.00 sec)

+------+------+-------------+------+
| id   | gid  | mva1        | mva2 |
+------+------+-------------+------+
|  100 | 1000 | 100,201     | 100  |
|  101 | 1001 | 101,202     | 101  |
|  102 | 1002 | 102,203     | 102  |
|  103 | 1003 | 103,204     | 103  |
|  104 | 1004 | 104,204,205 | 104  |
|  105 | 1005 | 105,206     | 105  |
+------+------+-------------+------+
6 rows in set (0.00 sec)

Here - deleting a document with id 100 from index named test:

📋
delete from test where id=100;

select * from test;
Response
Query OK, 1 rows affected (0.00 sec)

+------+------+-------------+------+
| id   | gid  | mva1        | mva2 |
+------+------+-------------+------+
|  101 | 1001 | 101,202     | 101  |
|  102 | 1002 | 102,203     | 102  |
|  103 | 1003 | 103,204     | 103  |
|  104 | 1004 | 104,204,205 | 104  |
|  105 | 1005 | 105,206     | 105  |
+------+------+-------------+------+
5 rows in set (0.00 sec)

Manticore SQL allows to use complex conditions for the DELETE statement.

For example here we are deleting documents that match full-text query dummy and have attribute mva1 with a value greater than 206 or mva1 values 100 or 103 from index named test:

SQL
📋
delete from test where match ('dummy') and ( mva1>206 or mva1 in (100, 103) );

select * from test;
Response
Query OK, 4 rows affected (0.00 sec)

+------+------+-------------+------+
| id   | gid  | mva1        | mva2 |
+------+------+-------------+------+
|  101 | 1001 | 101,202     | 101  |
|  102 | 1002 | 102,203     | 102  |
|  104 | 1004 | 104,204,205 | 104  |
|  105 | 1005 | 105,206     | 105  |
+------+------+-------------+------+
6 rows in set (0.00 sec)

Here is an example of deleting documents in cluster nodes4's index test:

📋
delete from nodes4:test where id=100;
Response
Array(
    [_index] => test
    [_id] => 100
    [found] => true
    [result] => deleted
)

Transactions

Manticore supports basic transactions when performing deleting and insertion into real-time and percolate indexes. That is: each change to the index first saved into internal changeset, and then actually committed to the index. By default each command is wrapped into individual automatic transaction, making it transparent: you just 'insert' something, and can see inserted result after it completes, having no care about transactions. However that behaviour can be explicitly managed by starting and committing transactions manually.

Transactions are supported for the following commands:

Transactions are not supported for:

Automatic and manual mode

SET AUTOCOMMIT = {0 | 1}

SET AUTOCOMMIT controls the autocommit mode in the active session. AUTOCOMMIT is set to 1 by default. With default you have not to care about transactions, since every statement that performs any changes on any index is implicitly wrapped into separate transaction. Setting it to 0 allows you to manage transactions manually. I.e., they will not be visible until you explicitly commit them.

Transactions are limited to a single RT or percolate index, and also limited in size. They are atomic, consistent, overly isolated, and durable. Overly isolated means that the changes are not only invisible to the concurrent transactions but even to the current session itself.

BEGIN, COMMIT, and ROLLBACK

START TRANSACTION | BEGIN
COMMIT
ROLLBACK

BEGIN statement (or its START TRANSACTION alias) forcibly commits pending transaction, if any, and begins a new one.

COMMIT statement commits the current transaction, making all its changes permanent.

ROLLBACK statement rolls back the current transaction, canceling all its changes.

Examples

Automatic commits (default)

insert into indexrt (id, content, title, channel_id, published) values (1, 'aa', 'blabla', 1, 10);
Query OK, 1 rows affected (0.00 sec)

select * from indexrt where id=1;
+------+------------+-----------+--------+
| id   | channel_id | published | title  |
+------+------------+-----------+--------+
|    1 |          1 |        10 | blabla |
+------+------------+-----------+--------+
1 row in set (0.00 sec)

Inserted value immediately visible by following 'select' statement.

Manual commits (autocommit=0)

set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

insert into indexrt (id, content, title, channel_id, published) values (3, 'aa', 'bb', 1, 1);
Query OK, 1 row affected (0.00 sec)

insert into indexrt (id, content, title, channel_id, published) values (4, 'aa', 'bb', 1, 1);
Query OK, 1 row affected (0.00 sec)

select * from indexrt where id=3;
Empty set (0.01 sec)

select * from indexrt where id=4;
Empty set (0.00 sec)

Here changes is NOT automatically committed. So, insertion is not visible even in the same session, since they're not committed. Also, despite absent BEGIN statement, transaction is implicitly started.

So, let's finally commit it:

commit;
Query OK, 0 rows affected (0.00 sec)

select * from indexrt where id=4;
+------+------------+-----------+-------+
| id   | channel_id | published | title |
+------+------------+-----------+-------+
|    4 |          1 |         1 | bb    |
+------+------------+-----------+-------+
1 row in set (0.00 sec)

select * from indexrt where id=3;
+------+------------+-----------+-------+
| id   | channel_id | published | title |
+------+------------+-----------+-------+
|    3 |          1 |         1 | bb    |
+------+------------+-----------+-------+
1 row in set (0.00 sec)

Now it is finished and visible.

Manual transaction

Using BEGIN and COMMIT you can define bounds of transaction explicitly, so no need to care about autocommit in this case.

begin;
Query OK, 0 rows affected (0.00 sec)

insert into indexrt (id, content, title, channel_id, published) values (2, 'aa', 'bb', 1, 1);
Query OK, 1 row affected (0.00 sec)

select * from indexrt where id=2;
Empty set (0.01 sec)

commit;
Query OK, 0 rows affected (0.01 sec)

select * from indexrt where id=2;
+------+------------+-----------+-------+
| id   | channel_id | published | title |
+------+------------+-----------+-------+
|    2 |          1 |         1 | bb    |
+------+------------+-----------+-------+
1 row in set (0.01 sec)

SET TRANSACTION

SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SERIALIZABLE }

SET TRANSACTION statement does nothing. It was implemented to maintain compatibility with 3rd party MySQL client libraries, connectors, and frameworks that may need to run this statement when connecting. They just goes across syntax parser and then returns 'ok'. Nothing usable for your own programs, just a stubs to make third-party clients happy.

mysql> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

✔ ️Searching