18 Aug
2011
18 Aug
'11
12:05 p.m.
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??
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)
18 Aug
18 Aug
4:42 p.m.
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:
--
Sergiu Dumitriu
http://purl.org/net/sergiu/
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)
4:56 p.m.
Le 18 août 2011 à 16:42, Sergiu Dumitriu a écrit :
Personally, I think that the index on
xwikilargestrings(xwl_value) is
not needed, but for all the other types of values they are useful.
Thanks Sergiu,
I think I agree with your analysis and I thank you for the 50 chars definition which is
certainly a safe limit.
Do I also understand that writing is then slowed down?
(such a b-tree would have quite many nodes, max 26^50, or? considering the entropy it
might be big)
paul
5:05 p.m.
On 08/18/2011 10:56 AM, Paul Libbrecht wrote:
Le 18 août 2011 à 16:42, Sergiu Dumitriu a écrit :
Yes, writing is a bit slowed down, but with a very big improvement in
terms of querying. Given that there are many more searches than writes,
and the write slowdown is in term of milliseconds while the query
speedup is in term of seconds, or even minutes when the database grows
larger, it is definitely a good trade-off.
--
Sergiu Dumitriu
http://purl.org/net/sergiu/
Personally, I think that the index on
xwikilargestrings(xwl_value) is
not needed, but for all the other types of values they are useful.
Thanks Sergiu,
I think I agree with your analysis and I thank you for the 50 chars definition which is
certainly a safe limit.
Do I also understand that writing is then slowed down?
(such a b-tree would have quite many nodes, max 26^50, or? considering the entropy it
might be big)
4831
days inactive
4831
days old
3 comments
2 participants
participants (2)
-
Paul Libbrecht -
Sergiu Dumitriu