Are there suitable indexes for this query?

Are there any suitable indexes to support the following query?

SELECT DISTINCT p.id
FROM p
INNER JOIN l ON p.id = l.p1_id OR p.id = l.p2_id
WHERE p.s = 'Active'
AND (
    (l.s IN (1, 7) AND l.rd <= CURDATE())
    OR
    (l.s = 2 AND MONTH(l.td) = MONTH(CURDATE()) AND YEAR(l.td) = YEAR(CURDATE()))
)

Tables:

CREATE TABLE p (
  id int(11) NOT NULL,
  s varchar(10) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE l (
  id int(11) NOT NULL,
  p1_id int(11) NOT NULL,
  p2_id int(11) NOT NULL,
  s int(11) NOT NULL,
  rd date NOT NULL,
  td date DEFAULT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (p1_id) REFERENCES p (id) ON UPDATE CASCADE,
  FOREIGN KEY (p2_id) REFERENCES p (id) ON UPDATE CASCADE
) ENGINE=InnoDB;

Explain:

+--+-----------+-----+----+-------------+---+-------+---+----+--------------------------------------------------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra                                             |
+--+-----------+-----+----+-------------+---+-------+---+----+--------------------------------------------------+
| 1|SIMPLE     |l    |ALL |             |   |       |   |3960|Using where; Using temporary                      |
| 1|SIMPLE     |p    |ALL |PRIMARY      |   |       |   |5091|Using where; Using join buffer (Block Nested Loop)|
+--+-----------+-----+----+-------------+---+-------+---+----+--------------------------------------------------+

I tried a number of single column and composite indexes based on the columns in the JOIN and WHERE clauses, and while indexes based on all the relevant columns are used by the DBMS, they don't make a difference to the number of rows evaluated.

Alternatively, could the query be rewritten in a more efficient way?

Edit:

Indexing on p.s provided some performance improvement, from 1.4 seconds down to 0.3 seconds.

ALTER TABLE p
ADD INDEX (s);

New explain:

```
+--+-----------+-----+----+-------------+---+-------+-----+----+------------------

It is a mess because of ORs.

Also, try to avoid hiding columns inside functions, including MONTH() and YEAR(). If l.td can never be bigger than CURDATE(), then change AND MONTH(l.td) = MONTH(CURDATE()) AND YEAR(l.td) = YEAR(CURDATE()) to AND l.td >= CONCAT(LEFT(CURDATE(), 7), '-01') (or some other expression that has l.td by itself compared to essentially a compile-time constant)

Let's focus first on this part of the query:

SELECT  l.id
    FROM  l
    WHERE  ( l.s IN (1, 7)
              AND  l.rd = CONCAT(LEFT(CURDATE(), 7), '-01') 
          )

Will this give you the same resultset?...

SELECT  l.id
    FROM  l
    WHERE  l.s IN (1, 7)
      AND  l.rd = CONCAT(LEFT(CURDATE(), 7), '-01')

Now, will it run faster with these:

INDEX(s,rd),
INDEX(s,td)

?

Before continuing, we need to know how many rows in each table, and what fraction of p is 'active'. And please provide EXPLAIN SELECT ... for that UNION.

More

INDEX(s) will be helpful since most are 'active'.

The UNION's explain seems good. It is probably faster than using OR. So now... Try to craft

SELECT ...
    FROM ( ...my UNION...) AS u
    JOIN p ON ...
    WHERE ... active

The principle is that we have sped up the OR, and gotten the result set down somewhat. Then the JOIN will filter out the few that are not 'active'.

StackExchange Database Administrators Q#173907, answer score: 2