wiki:Database Optimization

Version 6 (modified by earle, 14 years ago) (diff)

fix linebreaks

09:24:58 * Dom recommends inspecting http://www.wiki-toolkit.org/browser/wiki-toolkit/trunk/lib/Wiki/Toolkit/Setup
09:27:10 <@crschmidt> Dom: the problem is that the metadata table doesn't have a useful index --
09:27:52 <@crschmidt> the most common use cases are "Select all metadata for a node, given an id and a version", and "Select all nodes, given a metadata_type and metadata_value"
09:28:24 <@Dom> crschmidt: isn't that what, say, http://www.wiki-toolkit.org/browser/wiki-toolkit/trunk/lib/Wiki/Toolkit/Setup/MySQL.pm#L62 is doing?
09:28:52 <@crschmidt> No, that creates a single index -- those can't be split up
09:29:07 <@Dom> ah, right.
09:29:51 <@crschmidt> Creating an index on node_id,version would speed up the first case (I Think) and creating an index on metadata_name, metadata_value would speed up the second (I think)

many "I Think, but am not sure" disclaimers throughout.

Nick - if you have lots of metadata, then adding an index on node_id,version is likely to help (we no longer have metadata_name)

By metadata_name I meant metadata_type: http://crschmidt.net/openguides/explain.metadata.txt has an example of adding an index that seems to increase speed (but it's always hard to test these things).