Dear list, In my application, DBIx::Class is generating WHERE clauses using subqueries. With a lot of rows in the database this performs really badly in MariaDB [1], so I'm wondering whether there is a way to get DBIx::Class to produce the equivalent queries by joins or whether there are any other solutions.
Here is the code. The "tracks" table contains millions of rows, but there are only a very small number that will be deleted as a result of the query: $schema->resultset('Track')->search({ 'cd.artist_id' => 20, },{ join => 'cd', })->delete; Which produces this SQL: DELETE FROM tracks WHERE ( id IN ( SELECT * FROM ( SELECT me.id FROM tracks me JOIN cds cd ON cd.id = me.cd_id WHERE ( cd.artist_id = 20 ) ) _forced_double_subquery ) ); But this would be much, much quicker: DELETE tracks FROM tracks LEFT JOIN cds ON cds.id=tracks.cd_id WHERE cds.artist_id = 20 Any ideas how I can optimise this without writing raw SQL or changing databases? Thanks, Andy [1] https://stackoverflow.com/questions/7361174 _______________________________________________ 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