Avoid full table scan on a simple join using OR in MySQL

I have this schema

create table table1
(
    id        int          auto_increment primary key,
    name      varchar(2)   null,
    position  int          null,
);

create index table1_position
    on table1 (position);


create table table_2
(
    id              int auto_increment primary key,
    table1_id       int          null,
    position        int          null,
    constraint table_2_ibfk_1
        foreign key (table1_id) references table1 (id)
);

create index ix_table_2_position
    on table_2 (position);

create index table1_id
    on table_2 (table1_id);

So I added two index on column position of each tables.
Now I need to look for a range of position in BOTH table (by joining then and apply an OR query)

so I have this query

SELECT *
FROM table_1
    INNER JOIN table_2 ON table_1.id = table_2.table1_id
WHERE table_1.position BETWEEN 5000 AND 5500
   OR table_2.position BETWEEN 5000 AND 5500

But the Explain query output give me ALL (Full table scan)

id             1
select_type    SIMPLE
table          table_1
partitions
type           ALL
possible_keys  PRIMARY,table1_position
key
key_len
ref
rows           9929
filtered       100.0
Extra

If I change to an AND if give me the expected Range index scan

SELECT *
FROM table_1
    INNER JOIN table_2 ON table_1.id = table_2.table1_id
WHERE table_1.position BETWEEN 5000 AND 5500
   AND table_2.position BETWEEN 5000 AND 5500

id             1
select_type    SIMPLE
table          table_1
partitions
type           range
possible_keys  PRIMARY,pos_idx2
key            pos_idx2
key_len        5
ref
rows           1
filtered       100.0
Extra          Using index condition

But I need the OR statement here…How could I have mysql use a range scan index for an OR statement ? Could I improve my indexes here (I thought about a multi-values index on both position and table1_id -the foreign key, but it did’nt help and it performed a full table scan).

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

SELECT *
FROM table_1
INNER JOIN table_2 ON table_1.id = table_2.table1_id
WHERE table_1.position BETWEEN 5000 AND 5500

UNION ALL

SELECT *
FROM table_1
INNER JOIN table_2 ON table_1.id = table_2.table1_id
WHERE table_1.position NOT BETWEEN 5000 AND 5500
  AND table_2.position BETWEEN 5000 AND 5500

Also test

SELECT *
FROM table_1
INNER JOIN table_2 ON table_1.id = table_2.table1_id
WHERE NOT (    table_1.position NOT BETWEEN 5000 AND 5500
           AND table_2.position NOT BETWEEN 5000 AND 5500 )

Method 2

Presumably you want to avoid the table scan for reasons of performance.

So try swapping your OR for a UNION operation.

First, get the set of table1.id values you need with a subquery, like so.

    SELECT id FROM table_1 WHERE position BETWEEN 5000 AND 5500
    UNION
    SELECT table1_id FROM table_2 WHERE position BETWEEN 5000 AND 5500

The second part of the UNION retrieves the table_1.id values you need from table_2, by SELECTing the FK column.

Next, use that subquery to get your rows from table1.

SELECT * FROM table_1
 WHERE id IN (
    SELECT id FROM table_1 WHERE position BETWEEN 5000 AND 5500
    UNION
    SELECT table1_id FROM table_2 WHERE position BETWEEN 5000 AND 5500           
)

To make this quicker, add this compound index on table_2.

CREATE INDEX ix_table_2_position_table1id 
          ON table_2 (position, table1_id);

Notice that neither of your two single-column indexes on table2 are useful for this query.

Method 3

Performance problems with OR can often be solved with UNION:

SELECT *
FROM table_1
INNER JOIN table_2 ON table_1.id = table_2.table1_id
WHERE table_1.position BETWEEN 5000 AND 5500
UNION
SELECT *
FROM table_1
INNER JOIN table_2 ON table_1.id = table_2.table1_id
WHERE table_2.position BETWEEN 5000 AND 5500;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0