CODEWITHSUNDEEP
mysqlindexing
sunilsurana
sunilsurana

Reputation: 133

MySQL not using index when where condition have if clause

Below is the query 

SELECT * FROM mytable WHERE if(@type=1,col1,col2) = 1420070400;

@type is a session variable.

I have two indexes defined on both columns col1 and col2 but mysql does not uses in this case. Is there any way by which we can make mysql use index?

Below is explain result 

mysql> explain extended SELECT * FROM mytable WHERE if(@type=1,col1,col2) = 1420070400;
+----+-------------+-----------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table           | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | mytable         | ALL  | NULL          | NULL | NULL    | NULL | 1523 |   100.00 | Using where |
+----+-------------+-----------------+------+---------------+------+---------+------+------+----------+-------------+

Upvotes: 0

Views: 55

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

In general, databases (including MySQL) do not use indexes when columns are the arguments to functions.

You could try:

SELECT *
FROM mytable
WHERE (@type = 1 and col1 = 1420070400) or
      (@type <> 1 and col2 = 1420070400);

But, it is unlikely that this will use an index either. You can phrase the query like this:

SELECT *
FROM mytable
WHERE @type = 1 and col1 = 1420070400
UNION ALL
SELECT *
FROM mytable
WHERE @type <> 1 and col2 = 1420070400

A UNION ALL is fine, because only one of the subqueries should actually run. This version should make use of two separate indexes, one on mytable(col1) and the other on mytable(col2). Note: a single composite index is not sufficient.

Upvotes: 2

Related Questions