# Math

There is one more class of obfuscations that is smart and prevents proper index usage. Instead of using logic expressions it is using a calculation.

Consider the following statement. Can it use an index on `NUMERIC_NUMBER`?

``````SELECT numeric_number
FROM table_name
WHERE numeric_number - 1000 > ?``````

Similarly, can the following statement use an index on `A` and `B`—you choose the order?

``````SELECT a, b
FROM table_name
WHERE 3*a + 5 = b``````

Let’s put these questions into a different perspective; if you were developing an SQL database, would you add an equation solver? Most database vendors just say “No!” and thus, neither of the two examples uses the index.

#### On my Own Behalf

I make my living from training, other SQL related services and selling my book. Learn more at https://winand.at/.

You can even use math to obfuscate a condition intentionally—as we did it previously for the full text `LIKE` search. It is enough to add zero, for example:

``````SELECT numeric_number
FROM table_name
WHERE numeric_number + 0 = ?``````

Nevertheless we can index these expressions with a function-based index if we use calculations in a smart way and transform the `where` clause like an equation:

``````SELECT a, b
FROM table_name
WHERE 3*a - b = -5``````

We just moved the table references to the one side and the constants to the other. We can then create a function-based index for the left hand side of the equation:

``CREATE INDEX math ON table_name (3*a - b)`` Markus Winand is the SQL Renaissance Ambassador. He is on a mission to introduce developers to the evolution of SQL in the 21st century. Markus can be hired as trainer, speaker and consultant via winand.at. Visit my sibling! A lot changed since SQL-92! Stickers, coasters, books and coffee mugs. All you need for learning.