On 08/18/2011 06:05 AM, Paul Libbrecht wrote:

Hello fellow developers,

I had a look at the index that we use in Curriki and one of them is suspicious to me. As expected id and names are index keys for most table, that is correct.

However, one is suspect in several of the value tables: index on XWL_VALUE.

Do I understand correctly that this means these indexes is loaded into a btree (i.e. a ram-representation) of all the strings of xwiki, e.g. all the documents page content??

That is not "the documents page content"; xw*_value are indexes on object property values, like the first_name of user profiles, or the "published" status of a blog post. And these are needed to efficiently run queries like "get me all published blog posts". What is longer indeed is the xwikilargestrings value, which can hold very long strings. This is used to store text properties (like the blogpost content or summary). Still, the index on that column takes only the first 50 chars, as can be seen below:

create index xwl_value on xwikilargestrings (xwl_value(50));

It is not possible to have indexes on long strings. MySQL, for example, allows at most 1024 bytes (not characters) in an index. For UTF8 columns that means that at most 341 characters can be used in an index. Personally, I think that the index on xwikilargestrings(xwl_value) is not needed, but for all the other types of values they are useful.

This seems wrong to make an index for this. But http://platform.xwiki.org/xwiki/bin/view/AdminGuide/Database+Administration indeed recommends that:

...

create index xwl_value on xwikilongs (xwl_value); create index xwi_value on xwikiintegers (xwi_value); create index xws_value on xwikistrings (xws_value); create index xwl_value on xwikilargestrings (xwl_value(50));

So... can someone explain me why such indices are created? Would it be to honour the MySQL-based user-search?

thanks in advance

Paul

...

mysql> show index from xwikilargestrings; +-------------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | xwikilargestrings | 0 | PRIMARY | 1 | XWL_ID | A | 468592 | NULL | NULL | | BTREE | | | xwikilargestrings | 0 | PRIMARY | 2 | XWL_NAME | A | 937185 | NULL | NULL | | BTREE | | | xwikilargestrings | 1 | idl_value | 1 | XWL_VALUE | A | 312395 | 50 | NULL | YES | BTREE | | | xwikilargestrings | 1 | FK6661970F283EE295 | 1 | XWL_ID | A | 468592 | NULL | NULL | | BTREE | | | xwikilargestrings | 1 | FK6661970F283EE295 | 2 | XWL_NAME | A | 937185 | NULL | NULL | | BTREE | | +-------------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 5 rows in set (0.00 sec)

-- Sergiu Dumitriu http://purl.org/net/sergiu/