Optional MySQL Indexes
Reference
Each statement below indexes a column that XWiki reads but that its Hibernate mappings leave unindexed. The list is a menu rather than a checklist: measure the query that is slow, then add the one index that serves it. Create them as the wiki's own database user, and once per database, since XWiki gives every subwiki a database of its own.
General indexes
| Index | Table and column | What reads it |
|---|---|---|
| xwl_value | xwikilargestrings (xwl_value(50)) | The value of every Large String and TextArea object property. XWiki groups over this column when a Database List field offers the values already in use, and matches it with like in the REST keyword search. |
| xwd_parent | xwikidoc (xwd_parent(50)) | The queries that list the children or the orphans of a page test it for equality. |
| xwd_class_xml | xwikidoc (xwd_class_xml(20)) | Listing the XClasses of a wiki tests this column for an XML prefix. |
| xda_docid1 | xwikiattrecyclebin (xda_docid) | Every listing of a page's deleted attachments filters on it, while its neighbours xda_filename, xda_date and xda_doc_name are indexed by the mapping. |
| solr_iterate_all_documents | xwikidoc (XWD_WEB(500), XWD_NAME(253), XWD_LANGUAGE(5), XWD_VERSION(10)) | To work out which pages the Solr index is missing, XWiki walks the whole xwikidoc table reading exactly these columns. |
create index xwl_value on xwikilargestrings (xwl_value(50));
create index xwd_parent on xwikidoc (xwd_parent(50));
create index xwd_class_xml on xwikidoc (xwd_class_xml(20));
create index xda_docid1 on xwikiattrecyclebin (xda_docid);
create index solr_iterate_all_documents on xwikidoc (XWD_WEB(500), XWD_NAME(253), XWD_LANGUAGE(5), XWD_VERSION(10));Statistics indexes
XWiki's statistics module stores page views, referers and visits in three tables of its own, and storing them is switched off unless a wiki asks for it: the shipped xwiki.cfg sets xwiki.stats.default=0, because statistics cost space and write time. These nine indexes are therefore worth considering only on an instance that turned statistics on, and only once those three tables have grown.
| Index | Table and column |
|---|---|
| xws_number | xwikistatsdoc (XWS_NUMBER) |
| xws_classname | xwikistatsdoc (XWS_CLASSNAME) |
| xwr_number | xwikistatsreferer (XWR_NUMBER) |
| xwr_classname | xwikistatsreferer (XWR_CLASSNAME) |
| xwr_referer | xwikistatsreferer (XWR_REFERER(50)) |
| xwv_user_agent | xwikistatsvisit (XWV_USER_AGENT(255)) |
| xwv_cookie | xwikistatsvisit (XWV_COOKIE(255)) |
| xwv_classname | xwikistatsvisit (XWV_CLASSNAME) |
| xwv_number | xwikistatsvisit (XWV_NUMBER) |
create index xws_number on xwikistatsdoc (XWS_NUMBER);
create index xws_classname on xwikistatsdoc (XWS_CLASSNAME);
create index xwr_number on xwikistatsreferer (XWR_NUMBER);
create index xwr_classname on xwikistatsreferer (XWR_CLASSNAME);
create index xwr_referer on xwikistatsreferer (XWR_REFERER(50));
create index xwv_user_agent on xwikistatsvisit (XWV_USER_AGENT(255));
create index xwv_cookie on xwikistatsvisit (XWV_COOKIE(255));
create index xwv_classname on xwikistatsvisit (XWV_CLASSNAME);
create index xwv_number on xwikistatsvisit (XWV_NUMBER);FAQ
Does an upgrade create or remove these indexes?
No. XWiki creates only the indexes declared in its Hibernate mappings, so these are yours to create on each new database and to recreate if you ever drop them.
Which of them does my instance need?
Measure before creating any. Each one costs write time on a table XWiki writes to constantly, and only repays that on an instance whose page tree, recycle bin, Solr synchronization or statistics is measurably slow.
Are these indexes specific to MySQL?
The columns are not, but the statements are: the lengths in brackets are MySQL's prefix-index syntax for a long text column, which other databases express differently. See Optional Oracle Indexes for the Oracle equivalent.
