On Wed, 2008-01-16 at 17:30 +0800, joebert jacaba wrote:
> So in my case since I have a lot of functions that deal with dates i
> would need at least two int columns to replace the date column. Is
> this the widely accepted industry practice?
what are examples of what you're trying to do exactly?
A. you improved something by (probably) creating an
index on account code. likely the query that improved
was of the form
select [fieldlist] from [table] where acctcode = [constant]
(since you said you weren't doing any joins)
B. there was the query with the where clause that queried
only the month part of a date column.
select count(*) from table where month(due_date)=month(now());
in postgresql i would consider using a functional index (an
index on month(due_date), or, more likely, an index on
the year and month, as noted by some who replied to your
question. for one thing, as rob locke says, if the functional
index were on just month, there would be only 12 unique
keys, for any given month, you'd have 100M/12 rows (if this
was the 100M row table). such an index might be used by
the planner, but it need not be if a sequential scan would
be faster. apparently maxdb has functional indexes, but
only for user defined functions (but, as noted in:
http://blog.ulf-wendel.de/?p=107
it'd be trivial to wrap a built-in function inside a UDF
(not sure what the reason would be for the distinction between
UDF and built-in though, seems rather arbitrary).
i don't know if regular mysql has functional indexes. if
in fact it doesn't, then either you switch to maxdb, or to
postgresql, or you hack up those integer fields for month
and date.
i can hardly imagine that those integer date part fields
would be industry standard or best practice. you are
duplicating data in the row (the same month and year are
already in the due_date field). duplication is to
be avoided because it requires extra machinery to keep the
fields in sync. a lot of developers (most mysql developers,
probably) would try to keep that sync in the app. those with
more experience will set up triggers to keep the fields in
sync (so that it's not possible to update the integers with
month or year values that aren't the same as those in due_date.
those integer date part fields are basically hacks that
you'd put into the table to make certain operations/queries
more efficient. if there are better ways to get the
efficiency gains without bogotifying your data structure
that way, you should go with those better ways. if you
can use functional indexes, that's one way to go.
Michael and Rob have given you good options. Rob's will
work if in fact your queries really are supposed to be
WHERE due_date BETWEEN [some_start_date] AND [some_end_date]
(i just checked, mysql 5.x supports between for dates, if
it doesn't, you may need to use > and <. Michael's will work
if your queries are only on month and year. If your queries
really are only on month (something that doesn't seem to make
sense to me, but maybe there's a good business reason for it)
then either you create that integer month field or use a
database (or mysql variant, such as maxdb) that supports
functional indexes.
what other common queries do you run that have function
calls on date columns in the where clause?
tiger
_________________________________________________
Philippine Linux Users' Group (PLUG) Mailing List
[email protected] (#PLUG @ irc.free.net.ph)
Read the Guidelines: http://linux.org.ph/lists
Searchable Archives: http://archives.free.net.ph
