* non-unique index on the first parts of a longish text field (SUBSTRING(field)) to save disk space, while still allowing faster searches than a sequential scan.
* indexing on LOWER(field)/UPPER(field) to allow case-insensitive searches or case-insensitive unique constraint.
In what other scenario are you using functional index? Searching the archives, I found someone indexing a date as 'YYYYMMDD', probably to allow searching using LIKE 'YYYY%' or LIKE 'YYYYMM%'. I've also thought:
* creating a REVERSE_DOT(field) function for IP addresses and hostnames to allow wildcard searches (*.yahoo.com) using 'REVERSE_DOT(f) LIKE 'com.yahoo.%'.
* creating a HOSTPART(field) and LOCALPART(field) for email addressess;
Has anyone done something like this or other tricks using functional indexes? It seems potential to simplify data model (no need to store email addresses as host + part, etc).
-- dave
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
