Timeline for Most performant SQL query needed
Current License: CC BY-SA 3.0
7 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Dec 20, 2011 at 14:13 | history | migrated | from stackoverflow.com (revisions) | ||
| Dec 5, 2011 at 11:28 | comment | added | Nezreli | Ok, wikipedia says that Informix supports indexing expressions. Thanks, I'll go with computed columns. | |
| Dec 5, 2011 at 11:06 | comment | added | Quassnoi | @Nezreli: Not sure about Informix, but Oracle and PostgreSQL allow indexing expressions: CREATE INDEX ON (name, CASE datatype WHEN 'D' THEN CAST(value AS DATE) END, id). You should use the expression verbatim in you query for this index to work. MySQL does not support this (as well as creating computed columns, persisted or not, at all). | |
| Dec 5, 2011 at 11:04 | comment | added | Nezreli | I already have a datatype column. Not sure if all databases support creation of such computed fields (Informix and PostgreSQL ie.) hence my decision for more conservative approach. But, I'll look into it since it's more elegant solution. | |
| Dec 5, 2011 at 10:57 | comment | added | Quassnoi | @Nezreli: you can just keep the datatype in a separate column and use the computed columns like this: valueDate AS CASE DATATYPE WHEN 'D' THEN CAST(value AS DATE) END etc. There is no point in persisting the columns. Of course you should index them if you want faster ranged searches. | |
| Dec 5, 2011 at 10:51 | comment | added | Nezreli | Thanks. I was thinking of creating several columns in my table (one for int, nvarchar, char, varbinary, bool and datetime). Still not sure if I'm going to create indexes on them. | |
| Dec 2, 2011 at 16:04 | history | answered | Quassnoi | CC BY-SA 3.0 |