[Dbix-class] MySQL DELETE with LIMIT

[Peter Rabbitson]

On 08/18/2015 01:59 PM, Dmitry Bigunyak wrote:
> 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.


During limited testing with the second version, it was discovered it 
does not perform reliably across a variety of engines. Hence the 
subquery variant was chosen as the one that "works in virtually all 
circumstances"

It is not specified which engine are you using, but the emitted SQL 
makes me think you are on MySQL. This engine is known to suffer from a 
number of bugs where it would throw away the ORDER BY in partial 
UPDATE/DELETE cases (and LIMIT without ORDER BY makes no sense in almost 
any workload).


> 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 ?

I would be interested in a patch that allows for leaner SQL *provided* 
it detects a fixed-up MySQL version *AND* is covered with tests for each 
and every conceivable scenario.

If you would like to do the (considerable) legwork, I would be more than 
happy to provide you with extra information and guidance.

Cheers!