On Fri, 2003-08-01 at 08:15, Jeremy Browne wrote:
> For example, if you have "year(date_col) = 1990" in your where clause, MySQL
> will not use an index because it must calculate year for each date_col
> value.
Please forgive my reply. I realize I'm not directly answering your
question but I found this to be an extremely useful feature and thought
others would too.
Using Postgres you can create a functional index, meaning the results of
the function are indexed. In this example I have to create a helper
function because functional indexes can only index a function that
operates strictly on columns. You'll have to use that helper function in
your queries too for the index to be used, I believe.
CREATE FUNCTION year (DATE) RETURNS TEXT AS '
DECLARE result TEXT;
BEGIN
SELECT INTO result DATE_PART(''year'', $1);
RETURN result;
END;
' LANGUAGE 'plpgsql' IMMUTABLE;
CREATE INDEX year_idx ON table_name (year(date_col));
Corey
____________________
BYU Unix Users Group
http://uug.byu.edu/
___________________________________________________________________
List Info: http://uug.byu.edu/cgi-bin/mailman/listinfo/uug-list
