wiki:Database Optimization

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.

David - According to http://www.mysqlperformanceblog.com/2006/11/23/covering-index-and-prefix-indexes/ then "that creates a single index -- those can't be split up" is incorrect, and (node_id, version) does not need its own index.

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).

Last modified 14 years ago Last modified on Dec 12, 2006, 1:12:35 PM