确保 Informix DATETIME 列上使用索引

发布于 2024-09-12 07:14:42 字数 780 浏览 7 评论 0原文

假设我在 Informix DB 上有一个表：

create table password_audit (
    username CHAR(20),
    old_password CHAR(20),
    new_password CHAR(20),
    update_date DATETIME YEAR TO FRACTION));

我需要 update_date 字段以毫秒为单位（或者可能以秒为单位 - 同样的问题适用），因为同一天会有多次密码更新。

比方说，我有一个每晚的批处理作业，想要从今天的password_audit 表中检索所有记录。

为了提高性能，我想在 update_date 列上放置一个索引。如果我这样做：

CREATE INDEX pw_idx ON password_audit(update_date);

并运行此 SQL：（

SELECT *
FROM password_audit
WHERE DATE(update_date) = mdy(?,?,?)

其中 ?, ?, ? 是我的批处理作业传入的月、日和年），

那么我认为我的索引不会被使用 - 是这样吗？

我想我需要创建一个像这样的索引：

CREATE INDEX pw_idx ON password_audit(DATE(update_date));

- 是这样吗？

原文

Say I have a table on an Informix DB:

create table password_audit (
    username CHAR(20),
    old_password CHAR(20),
    new_password CHAR(20),
    update_date DATETIME YEAR TO FRACTION));

I need the update_date field to be in milliseconds (or seconds maybe - same question applies) because there will be multiple updates of the password on the same day.

Say, I have a nightly batch job that wants to retrieve all records from the password_audit table for today.

To increase performance, I want to put an index on the update_date column. If I do this:

CREATE INDEX pw_idx ON password_audit(update_date);

and run this SQL:

SELECT *
FROM password_audit
WHERE DATE(update_date) = mdy(?,?,?)

(where ?, ?, ? are the month, day and year passed in by my batch job)

then I don't think my index will be used - is that right?

I think I need to create an index something like this:

CREATE INDEX pw_idx ON password_audit(DATE(update_date));

- is that right?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

花辞树 2024-09-19 07:14:42

因为您强制服务器将两个值转换为 DATE，而不是 DATETIME，所以它可能不会使用索引。

您最好将 SQL 生成为：

SELECT *
  FROM password_audit
 WHERE update_date
       BETWEEN DATETIME(2010-08-02 00:00:00.00000) YEAR TO FRACTION(5)
           AND DATETIME(2010-08-02 23:59:59.99999) YEAR TO FRACTION(5)

这相当冗长。或者，也许更容易一些：

SELECT *
  FROM password_audit
 WHERE update_date >= DATETIME(2010-08-02 00:00:00.00000) YEAR TO FRACTION(5)
   AND update_date <  DATETIME(2010-08-03 00:00:00.00000) YEAR TO FRACTION(5)

这两个都应该能够使用 update_date 列上的索引。您可以尝试从文字中删除一些尾随零，但我认为您无法将它们全部删除 - 但看看 SET EXPLAIN ON 输出告诉您什么。

根据您的服务器版本，您可能需要在创建索引后运行 UPDATE STATISTICS，然后优化器才能使用它；与当前版本（11.10 及更高版本）相比，旧版本（例如 10.00 及更早版本）的 Informix 问题更大。

Because you are forcing the server to convert two values to DATE, not DATETIME, then it probably won't use an index.

You would do best to generate the SQL as:

SELECT *
  FROM password_audit
 WHERE update_date
       BETWEEN DATETIME(2010-08-02 00:00:00.00000) YEAR TO FRACTION(5)
           AND DATETIME(2010-08-02 23:59:59.99999) YEAR TO FRACTION(5)

That's rather verbose. Alternatively, and maybe slightly more easily:

SELECT *
  FROM password_audit
 WHERE update_date >= DATETIME(2010-08-02 00:00:00.00000) YEAR TO FRACTION(5)
   AND update_date <  DATETIME(2010-08-03 00:00:00.00000) YEAR TO FRACTION(5)

Both of these should be able to use the index on the update_date column. You can experiment with dropping some of the trailing zeroes from the literals, but I don't think you'll be able to remove them all - but see what the SET EXPLAIN ON output tells you.

Depending on your server version, you might need to run UPDATE STATISTICS after creating the index before the optimizer uses it at all; that is more of a problem on older (say 10.00 and earlier) versions of Informix than on the current (11.10 and later) versions.

回复收藏 0 原文