Ensuring index is used on Informix DATETIME column

Question

Hi. 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?

Answer 1

1. I Didn't see 'date_to_accounts_ni' defined in your password_audit table. What datatype/length is it?

2. Your first index on password_audit.update_date is adequate, why would you want to index (DATE(update_table))?

Answer 2

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.