如何在列的子字符串上创建索引？

Question

我有一个包含键值对的表，我想成为ale，以便有效地搜索：

SELECT * WHERE meta_key = "User ID" AND meta_value = "123userId";

但是，由于遗留需求，键和值NVARCHAR存储可能分别高达255个字符和1000个字符。在这样大的列上索引不仅代价很高，而且对某些db类型也有明显的限制。

我相信MySQL有一个允许LEFT-style子字符串索引的系统，如下所示：

CREATE INDEX ix_metadata_indexing_key_value ON metadata_indexing(meta_key, meta_value(255));

...however我们的系统必须支持MySQL、MSSQL和Oracle三种语言。这是正确的方法吗?如果是的话，我如何在MSSQL和Oracle上建立类似的索引？

Answer 1

正如@Larnu建议的那样，我们最终得到了一个新的计算meta_value_short列，需要为每个db类型分别生成该列。当询问时..。

如果长度小于或等于255，则为：与meta_value_short相比。

否则：首先将LEFT(value, 255)与meta_value_short进行比较，然后再与meta_value进行额外比较。

作为参考，计算的列定义如下：

MSSQL: ALTER TABLE [metadata_indexing] ADD meta_value_short AS (SUBSTRING(meta_value, 1, 255))

MySQL: ALTER TABLE metadata_indexing ADD meta_value_short VARCHAR(255) AS (SUBSTRING(meta_value, 1, 255));

甲骨文： ALTER TABLE metadata_indexing ADD meta_value_short AS (SUBSTR(meta_value, 1, 255));

Answer 2

如果您是在谈论WordPress，那么该索引只是解决方案的一部分。当深入到postmeta时，您需要一个以post_id开头的复合键。更多建议。

此外，如果数据足够可预测，您可能可以替换LONGTEXT将更文明的东西，如VARCHAR(150)。做SELECT MAX(LENGTH(meta_key)), MAX(LENGTH(meta_value)) FROM post_meta;。

(我也是，潘伊夫。尤其是WP的实施不力。)