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@lists.scsys.co.uk
