[Dbix-class] MySQL DELETE with LIMIT

[Dmitry Bigunyak]

Hi there,

I was very surprised to see the SQL statement which is generated when trying to delete some data with LIMIT clause.
My DBIC code:

my $rows = $rs->search(
    { state => 'D' },
    { rows => 10000 }
)->delete();

The SQL statement I get is this:

DELETE FROM cache WHERE (  id IN ( SELECT * FROM ( SELECT me.id FROM cache me WHERE ( state = ? ) LIMIT ? ) `_forced_double_subquery` )  ): 'D', '10000'

Which is a bit different from expected simple:

DELETE FROM cache WHERE state = 'D' LIMIT 10000;

The problem with the generated SQL is performance of course, on a table with just 100k records it's already 3 times slower than the second version.
If this is a known drawback and is it possible to get the normal simple query with DBIC or my only option here is to fall back to DBI with schema->storage->dbh ?

Would appreciate any suggestions,
Dmitry