如何在列的子字符串上创建索引? [英] How can I create an index on the substring of a column?

问题描述

我有一个包含键-值对的表，我希望可以高效地在这些表上进行搜索:

I have a table containing key-value pairs which I would like to be ale to search efficiently on:

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

但是，由于遗留要求，键和值NVARCHAR存储可能分别多达255个和1000个字符.在如此大的列上建立索引不仅成本高昂，而且还完全限制了某些数据库类型.

However due to a legacy requirement the key and value NVARCHAR storage might be as large as 255 and 1000 characters respectively. Indexing on such large columns is not only costly but also outright restricted on some db types.

我相信MySQL具有一个允许使用 LEFT 样式子字符串进行索引的系统，如下所示:

I believe MySQL has a system to allow indexes by a LEFT-style substring as follows:

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

...但是我们的系统必须支持MySQL，MSSQL和Oracle的全部三个.这是解决此问题的正确方法吗?如果是，我如何在MSSQL和Oracle上建立相似的索引?

...however our system must support all three of MySQL, MSSQL and Oracle. Is this the right way to go about this, and if so, how do I make similar indexes on MSSQL and Oracle?

推荐答案

正如@Larnu所建议的那样，我们最终获得了一个新的计算得出的 meta_value_short 列，该列需要针对每种数据库类型分别生成.查询时...

As @Larnu suggested we ended up with a new computed meta_value_short column which needed to be produced separately for each db type. When querying...

如果长度为255以下:只需与 meta_value_short 进行比较.

否则，:首先将 LEFT(value，255)与 meta_value_short 进行比较，然后再与 meta_value 进行比较.

Otherwise: first compare LEFT(value, 255) to meta_value_short, then additionally compare with meta_value.

计算列定义如下:

MSSQL: ALTER TABLE [metadata_indexing]添加meta_value_short AS(SUBSTRING(meta_value，1，255))

MySQL: ALTER TABLE元数据索引已添加meta_value_short VARCHAR(255)AS(SUBSTRING(meta_value，1，255));

Oracle: ALTER TABLE元数据索引已添加meta_value_short AS(SUBSTR(meta_value，1，255));

这篇关于如何在列的子字符串上创建索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！