Index columns with arithmetic operators

Question

I commonly make this query on my database SELECT * FROM Example_tab WHERE Column_a + Column_b < 10;.

Is it possible to create indexes based on Column_a + Column_b < 10 to speed up this lookup. I see it is possible to do it for oracle based db over here (by doing CREATE INDEX Idx ON Example_tab(Column_a + Column_b);).But I can't find it anywhere for postgresql or mysql.

Is it even possible to do it on databases other than oracle ? If yes, is it documented somewhere, what are they called (basically, what should I google to find them) ?

Thanks in advance for any help.

Answer 1

PostgreSQL has indexes on expressions (since version 7 I think!), check the documentation: Indexes on Expressions.

So, you could easily add an index on (column_a + column_b), with almost exactly the same syntax as in Oracle. The only difference would be the extra parentheses around the expression:

CREATE INDEX idx 
  ON Example_tab
  ((column_a + column_b)) ;

Note in case the above and the documentation isn't clear:
An expression can be as complicated as one wants and can include any number of operators or function calls.

---

MySQL does not have such indexes but they have a similar concept that can be used instead. MariaDB introduced VIRTUAL (computed) columns in their 5.2 version and the core MySQL just added them (called GENERATED) in their latest 5.7. See: Generated columns in MySQL.

So - in both MariaDB and MySQL - you can add a computed column and then an index on it. The syntax is similar but there are small differences. For MariaDB, the column can be either VIRTUAL or PERSISTED. If you want it indexed, it has to be PERSISTED:

ALTER TABLE Example_tab
   ADD COLUMN a_plus_b AS 
       (column_a + column_b) PERSISTED,
   ADD INDEX idx
       (a_plus_b) ;

For MySQL the column can be defined as either VIRTUAL or STORED. If you want it indexed, for versions before 5.7.8, it has to be STORED. For versions 5.7.8+ in can be either stored or not (in which case, the computed values are only stored once, in the index). See details on Indexes on Virtual columns:

ALTER TABLE Example_tab
   ADD COLUMN a_plus_b AS 
       (column_a + column_b),     -- optionally: STORED,
   ADD INDEX idx
       (a_plus_b) ;