MySQL DELETE with LIMIT

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

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
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...
Reply | Threaded
Open this post in threaded view
|

Re: MySQL DELETE with LIMIT

Peter Rabbitson-2
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!


_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...