[Dbix-class] MySQL DELETE with LIMIT

Dmitry Bigunyak icestar at inbox.ru
Tue Aug 18 11:59:13 GMT 2015

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 }

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,

More information about the DBIx-Class mailing list