Creating an index on an SQLite db using DBIx::Class

Question

I'm beginning to think that my DBIx::Class tables need indices - I have a few expensive queries over multiple joins and I'd like to see if I can optimise them a bit. Is there a way to create and maintain indices on tables within DBIx::Class?

Answer 1

The fine manual says to create an sqlt_deploy_hook function and use add_index to create the index; for example:

package My::Schema::Result::Artist;

 __PACKAGE__->table('artist');
 __PACKAGE__->add_columns(id => { ... }, name => { ... })

 sub sqlt_deploy_hook {
   my ($self, $sqlt_table) = @_;

   $sqlt_table->add_index(name => 'idx_name', fields => ['name']);
 }

 1;