CODEWITHSUNDEEP
mysqljoinindexing
arun
arun

Reputation: 11023

MySQL not using indexes when fields from different tables are in OR condition

Saw this: Can you use index in mysql using "col1 OR col2"? but thought I could ask this related question to keep the discussion simple and to the point.

I have two tables: lists and agree_and_disagree_count.

lists table has two timestamp fields modified_on and first_publish_date. Both these fields have their own indexes.

agree_and_disagree_count table also has a timestamp field date_created, which is also indexed.

In the following OR statement on fields in the same table, both the indexes are used.

mysql>  EXPLAIN SELECT * FROM lists l
    ->  WHERE (l.modified_on > '2013-01-07 12:50:51' OR
    ->   l.first_publish_date > '2013-01-07 12:50:51')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: l
         type: index_merge
possible_keys: modifiedon,first_publish_date
          key: modifiedon,first_publish_date
      key_len: 4,9
          ref: NULL
         rows: 2
        Extra: Using sort_union(modifiedon,first_publish_date); Using where

However if the fields are from different tables, it looks like the indexes are not used:

mysql> EXPLAIN SELECT * FROM lists l
    -> JOIN agree_and_disagree_count adc ON adc.list_id=l.list_id
    -> WHERE (l.modified_on > '2013-01-07 12:50:51' OR
    -> adc.date_created > '2013-01-07 12:50:51')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: adc
         type: ALL
possible_keys: list_id_type_id,idx_list_id,idx_date_created
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5114907
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: l
         type: eq_ref
possible_keys: PRIMARY,modifiedon
          key: PRIMARY
      key_len: 4
          ref: adc.list_id
         rows: 1
        Extra: Using where

Why?

I don't know how to type nicely formatted replies in comments, so the following is in response to @Iserni who has been so kind to help me out:

I did the following:

use test;

-- create tables
create table lists (
list_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
modified_on TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
first_publish_date DATETIME NULL DEFAULT NULL,
primary key (list_id)
) ENGINE=InnoDB;

create table agree_and_disagree_count (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
list_id int unsigned not null,
date_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
primary key (id)
) ENGINE=InnoDB;

-- create indices
create index index_modified_on on lists (modified_on);
create index index_first_publish_date on lists (first_publish_date);

create index index_data_created on agree_and_disagree_count (date_created);

-- load data
insert into lists (list_id, modified_on, first_publish_date) values 
(1, now(), now()),
(2, now(), now()),
(3, now(), now());

insert into agree_and_disagree_count (list_id, date_created) values 
(1, now()), (1, now()), (2, now());

-- query
EXPLAIN SELECT * FROM lists l JOIN agree_and_disagree_count adc 
ON adc.list_id=l.list_id WHERE (l.modified_on > '2013-01-01 12:50:51'
OR adc.date_created > '2013-01-07 11:50:51');

+----+-------------+-------+------+----------------------------------+------+---------+------+------+--------------------------------+
| id | select_type | table | type | possible_keys                    | key  | key_len | ref  | rows | Extra                          |
+----+-------------+-------+------+----------------------------------+------+---------+------+------+--------------------------------+
|  1 | SIMPLE      | l     | ALL  | PRIMARY,index_modified_on        | NULL | NULL    | NULL |    3 |                                |
|  1 | SIMPLE      | adc   | ALL  | index_data_created,index_list_id | NULL | NULL    | NULL |    3 | Using where; Using join buffer |
+----+-------------+-------+------+----------------------------------+------+---------+------+------+--------------------------------+

-- create new indexes suggested by Iserni
CREATE INDEX adc_test_ndx ON agree_and_disagree_count (list_id, date_created);
CREATE INDEX l_test_ndx ON lists (list_id, modified_on);

-- query
EXPLAIN SELECT * FROM lists l JOIN agree_and_disagree_count adc 
ON adc.list_id=l.list_id WHERE (l.modified_on > '2013-01-01 12:50:51'
OR adc.date_created > '2013-01-07 11:50:51');

+----+-------------+-------+-------+-----------------------------------------------+--------------+---------+------+------+--------------------------------+
| id | select_type | table | type  | possible_keys                                 | key          | key_len | ref  | rows | Extra                          |
+----+-------------+-------+-------+-----------------------------------------------+--------------+---------+------+------+--------------------------------+
|  1 | SIMPLE      | adc   | index | index_data_created,index_list_id,adc_test_ndx | adc_test_ndx | 8       | NULL |    3 | Using index                    |
|  1 | SIMPLE      | l     | ALL   | PRIMARY,index_modified_on,l_test_ndx          | NULL         | NULL    | NULL |    3 | Using where; Using join buffer |
+----+-------------+-------+-------+-----------------------------------------------+--------------+---------+------+------+--------------------------------+

Looks like full scan still happening?

Look at @Iserni's comment below the accepted answer.

Upvotes: 2

Views: 261

Answers (1)

LSerni
LSerni

Reputation: 57408

Once the JOIN on list_id is under way, MySQL has no way to "link" the list identifier with the fields used in the query and saved in the index, for no index contains both the ID and those fields; and you can't run joins on the indexes.

Try creating these two indexes:

CREATE INDEX adc_test_ndx ON agree_and_disagree_count (list_id, date_created);
CREATE INDEX l_test_ndx ON lists (list_id, modified_on);

and retry with EXPLAIN. On my system I tried creating two minimal tables with two records each:

mysql> EXPLAIN SELECT * FROM lists l JOIN agree_and_disagree_count adc 
ON adc.list_id=l.list_id WHERE (l.modified_on > '2013-01-01 12:50:51'
OR adc.date_created > '2013-01-07 11:50:51')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: l
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: adc
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using where; Using join buffer
2 rows in set (0.00 sec)

and then:

mysql> CREATE INDEX adc_test_ndx ON agree_and_disagree_count (list_id, date_created);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX l_test_ndx ON lists (list_id, modified_on);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM lists l JOIN agree_and_disagree_count adc 
ON adc.list_id=l.list_id WHERE (l.modified_on > '2013-01-01 12:50:51'
OR adc.date_created > '2013-01-07 11:50:51')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: l
         type: index
possible_keys: l_test_ndx
          key: l_test_ndx
      key_len: 9
          ref: NULL
         rows: 2
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: adc
         type: ref
possible_keys: adc_test_ndx
          key: adc_test_ndx
      key_len: 5
          ref: test.l.list_id
         rows: 1
        Extra: Using where; Using index
2 rows in set (0.00 sec)

mysql>

As expected, I now get indexing on both tables.

Can you post the tables' structure and existing indexes or, even better, a SQLFiddle?

Upvotes: 3

Related Questions