是否有任何合适的索引来支持以下查询？
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()))
)
表：
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;
解释：
+--+-----------+-----+----+-------------+---+-------+---+----+--------------------------------------------------+
|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)|
+--+-----------+-----+----+-------------+---+-------+---+----+--------------------------------------------------+
我尝试了一些基于 JOIN 和 WHERE 子句中的列的单列索引和复合索引，虽然 DBMS 使用了基于所有相关列的索引，但它们对评估的行数没有影响。
或者，能否以更有效的方式重写查询？
编辑：
ps 上的索引提供了一些性能改进，从 1.4 秒减少到 0.3 秒。
ALTER TABLE p
ADD INDEX (s);
新说明：
+--+-----------+-----+----+-------------+---+-------+-----+----+--------------------------------------------------------+
|id|select_type|table|type|possible_keys|key|key_len|ref |rows|Extra |
+--+-----------+-----+----+-------------+---+-------+-----+----+--------------------------------------------------------+
| 1|SIMPLE |p |ref |PRIMARY,s |s |32 |const|5058|Using where; Using index; Using temporary |
| 1|SIMPLE |l |ALL | | | | |3960|Range checked for each record (index map: 0x6); Distinct|
+--+-----------+-----+----+-------------+---+-------+-----+----+--------------------------------------------------------+
是否可以进一步改进？
编辑 2：
应用建议索引解释 Rick James 的 UNION 查询：
+--+------------+----------+-----+-------------+---+-------+-----+----+------------------------+
|id|select_type |table |type |possible_keys|key|key_len|ref |rows|Extra |
+--+------------+----------+-----+-------------+---+-------+-----+----+------------------------+
| 1|PRIMARY |l |range|srd,std |srd|7 | | 733|Using where; Using index|
| 2|UNION |l |range|srd,std |std|7 | | 2|Using where; Using index|
| |UNION RESULT|<union1,2>|ALL | | | | | |Using temporary |
+--+------------+----------+-----+-------------+---+-------+-----+----+------------------------+
一些统计数据：
SELECT s, COUNT(*) FROM l GROUP BY s
+-+--------+
|s|COUNT(*)|
+-+--------+
|1| 733|
|2| 3222|
|8| 5|
+-+--------+
=3960
SELECT s, COUNT(*) FROM p GROUP BY s
+--------+--------+
|s |COUNT(*)|
+--------+--------+
|Active | 5059|
|Inactive| 32|
+--------+--------+
=5091
ls 中的 8 是正确的，不应包含在上面的查询结果中。即使没有 ls=7 的行，我也需要包括这种可能性。
预期结果集包含 1144 条记录。
最后：
根据 Rick James 的建议，下面的查询加上索引pon(s, td)和中的索引相结合(r, td)，执行效率达到我希望达到的水平（~50 毫秒）：
SELECT DISTINCT p.id
FROM (
SELECT p1_id AS id
FROM l
WHERE s = 1 AND rd <= CURDATE()
UNION ALL
SELECT p2_id
FROM l
WHERE s = 7 AND rd <= CURDATE()
UNION ALL
SELECT p1_id AS id
FROM l
WHERE s = 1 AND rd <= CURDATE()
UNION ALL
SELECT p2_id
FROM l
WHERE s = 7 AND rd <= CURDATE()
UNION ALL
SELECT p1_id
FROM l
WHERE s = 2 AND td >= CONCAT(LEFT(CURDATE(), 7), '-01') AND td < CONCAT(LEFT(CURDATE(), 7), '-01') + INTERVAL 1 MONTH
UNION ALL
SELECT p2_id
FROM l
WHERE s = 2 AND td >= CONCAT(LEFT(CURDATE(), 7), '-01') AND td < CONCAT(LEFT(CURDATE(), 7), '-01') + INTERVAL 1 MONTH
) x
JOIN p ON p.id = x.id
WHERE p.s = 'Active'