CODEWITHSUNDEEP
mysqlindexingwhere-clauseclause
KillDash9
KillDash9

Reputation: 909

not using index in where clause

If i have the following in the where clause :

( approps_precio * moneda_valor BETWEEN 2000 AND 6000)

it does not use the idx_approps_precio index.

But

( approps_precio BETWEEN 2000 AND 6000)

does it very well.

Is there a way to add the * operation and still use the index?.

Thanks in advance.

Upvotes: 0

Views: 319

Answers (3)

eggyal
eggyal

Reputation: 125835

I've been digging around to find some official documentation to support what Paul DuBois (albeit a member of the MySQL documentation team) says in the fifth chapter "Query Optimization" of his book MySQL (Developer's Library):

Make indexed columns stand alone in comparison expressions. If you use a column in a function call or as part of a more complex term in an arithmetic expression, MySQL can't use the index because it must compute the value of the expression for every row. Sometimes this is unavoidable, but many times you can rewrite a query to get the indexed column to appear by itself.

The best I can find is the section on Optimizer Transpositions in the Internals manual:

However, MySQL does not support transpositions where arithmetic exists. Thus:

WHERE 5 = -column1

is not treated the same as:

WHERE column1 = -5

Transpositions to expressions of the form column = constant are ideal for index lookups.

In this case, one can take advantage of the index on approps_precio by rewriting the filter criteria by dividing through by moneda_valor:

WHERE approps_precio BETWEEN 2000/moneda_valor AND 6000/moneda_valor

Note that MySQL will still need to evaluate the division for each record, so I'm not convinced that much will be saved.

Upvotes: 1

Joni
Joni

Reputation: 111219

Is moneda_valor a constant? If not, an index on approps_precio is useless when you look for values of approps_precio multiplied by moneda_valor.

Upvotes: 1

Maxim Krizhanovsky
Maxim Krizhanovsky

Reputation: 26699

The * operation makes your value 0 and hence outsite of the range when moneda_valor is null. So you can rewrite it as:

approps_precio BETWEEN 2000 AND 6000 and moneda_valor IS NOT NULL

Upvotes: 0

Related Questions