The documentation you quoted did not say you cannot use LIMIT with multiple tables, it said you cannot use LIMIT with multi-table DELETE syntax.

DELETE t1.* FROM test1 t1 WHERE ID=1

is the syntax for multi-table DELETE. The single-table DELETE syntax would've been

DELETE FROM test1 WHERE ID=1

What about hints? Single table delete doesn't work with hints like "use index"

MariaDB [test]> DELETE FROM test1 use index(ix_id) WHERE ID=1;         

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'use index(ix_id) WHERE ID=1' at line 1

MariaDB [test]> DELETE FROM test1 use index(ix_id) WHERE ID=1 LIMIT 10;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'use index(ix_id) WHERE ID=1 LIMIT 10' at line 1

MariaDB [test]>

If we can try to use alias then it works.

MariaDB [test]> DELETE t1.* FROM test1 t1 use index(ix_id) WHERE ID=1;

Query OK, 0 rows affected (0.001 sec)

But then LIMIT doesn't work with aliases.

MariaDB [test]> DELETE t1.* FROM test1 t1 use index(ix_id) WHERE ID=1 LIMIT 10;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LIMIT 10' at line 1

MariaDB [test]> 

So what if we want to use both Hints and LIMIT with Delete?

We could probably support with very little work:

DELETE t1.* FROM test1 t1 use index(ix_id) WHERE ID = 1 limit 10;

When there is only one table from which we delete.
For this case we only have to add limit support to multi_delete::send_data()
and add some checks that only one table is used.

In theory it is possible to add hints to single table delete, but I think for this case it is easier to add limit to multi-table delete

igor Do I remember correctly that you made switching to SELECT-Like DELETE in some conditions, maybe we can do the same in case hints and LIMIT?

There are two ways to solve this request:

• Add hints to single table delete and single table update.
• Add support for ORDER BY and LIMIT to multi table delete syntax using only one table. We need to also support ORDER
  BY as otherwise the statement is is not replication safe.

I am leaning to adding hints to single table delete and single table update as this is easier to do and should solve the problem for the user

I looked on the support tickets, it is clear that user need ORDER BY (and use USE INDEX instead) to ordering for LIMIT

BTW maybe it is really syntax error becouse this is working:

create table t1 (id int primary key);

DELETE FROM t1 WHERE ID=1 ORDER BY id LIMIT 10;

drop table t1;

and hints are working:

create table t2 (id int, index xid(id));

create table t1 (id int primary key);

DELETE t1.* FROM  t1,t2 use index(xid);

DELETE t2.* FROM  t2 use index(xid);

drop table t1, t2;

So it can be 2 separate asks:

1. add LIMIT / ORDER BY to multi delete statements
2. add hints support to single table delete

niljoshi which of two or both the user need?

Looking on the examples it looks like first part is needed, but I will not be surprised if both are

Actually hints are for joining tables, so I suspect they are not useful in single table operation.

psergei Can be hints (like USE INDEX(XXX)) be somehow useful for singletable SELECT/UPDATE/DELETE (for example used for WHERE condition resolwing , i.e. used table access method) ?

update from psergei : the hints can be useful for single table operation

Hi sanja, customer was looking for single table delete syntax with using index and psergei also confirmed that hints can be useful for single table operation too so I think it would be great if both single delete and multi-delete scenario will be fixed.

niljoshi why then all examples made by you in the description are multidelete syntax? Please next time make your part of work of descripting what is really needed.

sanja The original issue was about LIMIT not working together with a table alias in a single table statement. The original query also had a hint (USE INDEX) in the same single table DELETE statement.

So both should work with LIMIT.