如何在mysql中索引innodb文本? [英] How to index innodb text in mysql?
问题描述
我正在尝试索引mysql innodb-table中的文本字段，并且看起来像哈希索引是这样的方式:
I'm trying to index a text field in mysql innodb-table and seems like hash index is the way to go like this:
CREATE INDEX teksti_index USING HASH ON maili_teksti(teksti(100));
因此，这是否意味着mysql会使用该字段的前100个字符并计算哈希值(然后索引该哈希值).如果将数字100更改为200，索引的大小是否相同?
So, does this mean that mysql takes first 100 characters of the field and calculates the hash (and then indexes the hash). Is the size of the index same if I change the number 100 to 200?
如果我想优化这种命令，这是正确的方法:
And ... is this a right way to go if I want to optimize this kind of commands:
SELECT count(*) from teksti where teksti='random text';
推荐答案
在处理CHAR，VARCHAR和TEXT列时(正如您在处理TEXT时)，您分配的前缀长度创建索引时，将使用前X个字符创建哈希-正是您的想法(
When dealing with CHAR, VARCHAR, and TEXT columns (as you are dealing with TEXT), the prefix-length you assign when creating the index will create a hash using the first X characters - exactly how you're thinking (source).
在处理标准索引时，更改索引数据的大小也应更改索引大小(即-添加字符应增加索引；删除应减少).在处理HASH索引时，这是一个猜测，因为我找不到有关它的特定文档，所以我会假设由于性质的原因，它不会增加(至少不会增加很多)哈希算法.
When dealing with a standard index, changing the size of the data that's indexed should also change the size of the index (i.e. - adding characters should increase the index; removing should decrease). When dealing with a HASH-index, and this is a guess because I cannot find specific documentation regarding it, I would assume that it doesn't increase (at least, not by much) due to the nature of hashing-algorithms.
HASH索引仅适用于=和<=>运算符，因此teksti='random text'的示例用法非常适合此类索引(
HASH-indexes are only applied to the = and <=> operators, so your sample usage of teksti='random text' is perfect for this type of index (source). If you need to use other operators, such as the LIKE, < or > operators, you may have to consider switching to a B-TREE index instead.
作为一个完整的替代选项，您可以签出 Full-Text Search Functions 页面上的文档指出FULLTEXT只能与
As a complete alternative option, you could check out FULLTEXT index. This provides a large amount of matching capabilities, though it's fairly robust and may be too much. The documentation on the Full-Text Search Functions page states that FULLTEXT can only be used with MyISAM, however, Section 14.2.4.12.3 on the InnoDB Table and Index documentation page covers FULLTEXT indexes with InnoDB - so, this may or may not be available =P.
这篇关于如何在mysql中索引innodb文本?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
