OR Conditional with indexes

Lets use the OR in the WHERE on the file table.

mysql> EXPLAIN SELECT title, rental_duration, length

 -> FROM film

 -> WHERE rental_duration = 6 OR length = 100;

+----+-------------+-------+------------+------+--------------------------------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------------+------+--------------------------------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | film | NULL | ALL | idx_film_length,idx_film_length_desc | NULL | NULL | NULL | 1000 | 10.64 | Using where |

+----+-------------+-------+------------+------+--------------------------------------+------+---------+------+------+----------+-------------+

Notice they are two possible keys for the query but none of the keys was used.

Create and index for both columns

mysql> CREATE INDEX idx_film_rental_duration ON film (rental_duration);

Query OK, 0 rows affected (0.07 sec)

Records: 0 Duplicates: 0 Warnings: 0
``

Re-run the explain again and check the results

mysql> EXPLAIN SELECT title, rental_duration, length FROM film WHERE rental_duration = 6 OR length = 100;

+----+-------------+-------+------------+------+--------------------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------------+------+--------------------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | film | NULL | ALL | idx_film_rental_duration | NULL | NULL | NULL | 1000 | 28.00 | Using where |

+----+-------------+-------+------------+------+--------------------------+------+---------+------+------+----------+-------------+

Notice there's a warning this time. Show warnings

mysql > show warnings;

+-------+------+--------

Notice the possible key is idx_film_rental_duration and indexes are used and we did a table scan.

Create an index on both columns

Lets create an index for both rental_duration and length

mysql> CREATE INDEX idx_film_rental_duration_length ON film (rental_duration,length);

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

Notice they are now two possible keys idx_film_rental_duration and idx_film_rental_length but no index was used to run the query. By why?

Create another index and change the order

Now we will change the order of index and check if there's an difference

mysql> CREATE INDEX idx_film_rental_duration_length ON film (rental_duration,length);

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

and we also change the order of the columns in the SELECT query

mysql> EXPLAIN SELECT title, rental_duration, length

 -> FROM film

 -> WHERE rental_duration = 6 OR length = 100;

+----+-------------+-------+------------+------+----------------------------------------------------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------------+------+----------------------------------------------------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | film | NULL | ALL | idx_film_rental_duration,idx_film_rental_duration_length | NULL | NULL | NULL | 1000 | 28.00 | Using where |

+----+-------------+-------+------------+------+----------------------------------------------------------+------+---------+------+------+----------+-------------+

and create another index

mysql> CREATE INDEX idx_film_length_rental_duration ON film (length,rental_duration);

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

and re-run the query

EXPLAIN SELECT title, rental_duration, lengthFROM filmWHERE length = 100 OR rental_duration = 6;

and still there's no index to satisfy the query.

Run the queries individually

mysql> EXPLAIN SELECT title, rental_duration, length FROM film WHERE rental_duration = 6\G;

*************************** 1. row ***************************

 id: 1

 select_type: SIMPLE

 table: film

 partitions: NULL

 type: ref

possible_keys: idx_film_rental_duration,idx_film_rental_duration_length

 key: idx_film_rental_duration

 key_len: 1

 ref: const

 rows: 212

 filtered: 100.00

 Extra: NULL

1 row in set, 1 warning (0.01 sec)

and

mysql> EXPLAIN SELECT title, rental_duration, length FROM film WHERE length = 100;

+----+-------------+-------+------------+------+---------------------------------+---------------------------------+---------+-------+------+----------+-------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------------+------+---------------------------------+---------------------------------+---------+-------+------+----------+-------+

| 1 | SIMPLE | film | NULL | ref | idx_film_length_rental_duration | idx_film_length_rental_duration | 3 | const | 12 | 100.00 | NULL |

+----+-------------+-------+------------+------+---------------------------------+---------------------------------+---------+-------+------+----------+-------+

Drop the indexes and clean up

DROP INDEX idx_film_length_rental_duration ON film;DROP INDEX idx_film_rental_duration_length ON film;DROP INDEX idx_film_length ON film;DROP INDEX idx_film_rental_duration ON film;