[Dbix-class] Mysql / mariadb and subqueries

[Andrew Beverley]

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