Informix中的组索引 [英] Group Indexing in Informix

问题描述

我有一个名为 hitlist 的表，它有3列：

  int id 
 long hitlisted_date 
 long deleted_date 
  

我将查询此信息表格基于以下列：

  histlisted_date（频繁）
 hitlisted_date&& deleted_date（频繁）
 deleted_date（不常见）
  

在这种情况下，什么样的索引我应该使用吗？

1. hitlisted_date的单独索引& deleted_date


2. 上的群组索引hitlisted_date & deleted_date

更新

该表只有1000 - 5000行。

这些是将要使用的查询模式。

1）hitlisted_date BETWEEN

2）hitlisted_date<

3）deleted_date = -1 and hitlisted_date< = < br>
4）deleted_date> 0

对于上述模式，这些索引就足够了吗？

1. CREATE INDEX i1_hitlist ON结果列表（hitlisted_date）;


2. CREATE INDEX i2_hitlist ON结果列表（deleted_date，hitlisted_date）;




解决方案

由于 hitlisted_date 并且经常使用该组合，您需要一个复合材料带有 hitlisted_date 的两列上的索引：

  CREATE INDEX i1_hitlist ON hitlist（hitlisted_date，deleted_date）; 
  

此索引可以（并且将）用于 hitlisted_date 独自或两个日期。





您可能会发现只需<$ c $就可以获得第二个索引c> deleted_date ：

  CREATE INDEX i2_hitlist ON hitlist（deleted_date）; 
  

这可用于仅搜索 deleted_date 。如果您有时对单个删除日期和一系列命中日期进行搜索，那么您可能会发现使用与 i1_hitlist 相反的复合索引更好：

  CREATE INDEX i2_hitlist ON hitlist（deleted_date，hitlisted_date）; 
  

这不太可能是一个帮助，但唯一可以确定的方法是试试看。这取决于您的查询模式以及您的查询使用的实际条件。





仅在 hitlisted_date ;它只会妨碍优化器（因为它必须查看两个索引并确定哪个更好，因为在插入，更新和删除行时还有更多工作要做）。命中日期不太可能是唯一索引。如果可以的话，那么保留单列索引以及重复索引就有另外一个原因。 （另请参阅是如果（A，B，C）上有索引，则（A，B）上的索引是多余的。）





更改索引后，请确保统计数据是最新的（这些天或多或少是自动的，但它曾经很重要），然后使用SET EXPLAIN on运行查询以检查是否正在使用索引（以及正在使用哪些索引）。




I have a table called hitlist, which has 3 columns:

int id
long hitlisted_date
long deleted_date

I will be querying this table based on these columns:

histlisted_date (frequent)
hitlisted_date && deleted_date (frequent)
deleted_date (not frequent)

In this situation, what kind of index should I use?

1. Separate index on hitlisted_date & deleted_date
2. Group index on hitlisted_date & deleted_date

UPDATE

The table will have just 1000 - 5000 rows.
These are the query patterns that will be used.

1) hitlisted_date BETWEEN
2) hitlisted_date <
3) deleted_date = -1 and hitlisted_date <=
4) deleted_date > 0

For the above patterns, these indexes would suffice?

1. CREATE INDEX i1_hitlist ON hitlist(hitlisted_date);
2. CREATE INDEX i2_hitlist ON hitlist(deleted_date, hitlisted_date);

解决方案

Since the hitlisted_date and the combination will be used frequently, you want a composite index on the two columns with hitlisted_date first:

CREATE INDEX i1_hitlist ON hitlist(hitlisted_date, deleted_date);

This index can (and will) be used for queries with a suitable condition on hitlisted_date on its own, or for the two dates.

You may find it beneficial to have a second index on just deleted_date:

CREATE INDEX i2_hitlist ON hitlist(deleted_date);

This can be used for searches on just deleted_date. If you sometimes do searches on a single deleted date and a range of hitlisted dates, then you might find it better to use a compound index that's the reverse of i1_hitlist:

CREATE INDEX i2_hitlist ON hitlist(deleted_date, hitlisted_date);

It's unlikely to be a help, but the only way to be sure is to try it and see. It depends on your query patterns, and the actual conditions your queries use.

There's no real virtue in an index on just hitlisted_date; it just gets in the way of the optimizer (because it has to look at two indexes and decide which is better, and because there is more work to do as rows are inserted, updated and deleted). It is unlikely that the hitlisted date could be a unique index. If it could, then there'd be a separate reason for keeping the single-column index as well as the duplicates index. (See also Is an index on (A,B) redundant if there is an index on (A, B, C).)

After you change indexes, make sure the statistics are up to date (more or less automatic these days, but it used to be important), and then run queries with SET EXPLAIN on to check that the indexes are being used (and which indexes are being used).

这篇关于Informix中的组索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！