Missing index for wiki_references causes high loading times

[sattlerc]

On page cache expiration (happening after every edit or currently even when a user locks a page by clicking on "edit"), page categories are looked up. An example query is:

SELECT DISTINCT wiki_references.referenced_name
FROM wiki_references LEFT OUTER JOIN pages ON wiki_references.page_id = pages.id
WHERE wiki_references.link_type = 'C' AND pages.web_id = '1';

For larger wikis, this is really slow (3-4s in the case of the nlab), even if there are few pages that live in page categories.

A reason for the slowness is that the table wiki_references doesn't have an index for link_types.
Creating this index solves the problem:

MariaDB [nlab]> create index index_wiki_references_on_link_type on wiki_references (link_type);
Query OK, 10072562 rows affected (2 min 10.78 sec)
Records: 10072562  Duplicates: 0  Warnings: 0

(A more principled solution would be to have a table for page categories.)

[distler]

The Wiki_References table is, admittedly a bit of a dogs-breakfast. We could have separate tables for each link_type.

But, failing that, here's a Migration that will add the desired index

class AddWikireferenceTypeIndex < ActiveRecord::Migration
  def self.up
    add_index :wiki_references, ["link_type"], :name => "index_wiki_references_on_link_type"
  end

  def self.down
    remove_index :wiki_references, :name => "index_wiki_references_on_link_type"
  end
end

Does that help? If so, I will commit that.

[sattlerc]

Looks good!

[distler]

The migrations are run automatically when you

ruby bundle exec rake upgrade_instiki

Or you can run them manually with

ruby bundle exec rake db:migrate RAILS_ENV=production

I will commit this migration after I release Instiki 0.30.3.