我有3张桌子
表1具有ID单元格
表2具有ID单元格
Table3具有id1单元格->对应于Table1.id
Table3具有id2单元格->对应于Table2.id
Table3有一个时间戳，用于仅查看数据的最后一天
表1和表2具有更多的数据，仅需要返回。
SELECT
t1.name AS t1name,
t1.id AS t1id,
t2.name AS t2name,
t2.id AS t2id,
t2.surname AS t2surname,
t2.served AS t2served,
t2.reported AS t2reported,
COUNT(CASE WHEN t3.id1 IS NOT NULL AND t3.id2 IS NOT NULL THEN 1 END) AS t3hits
FROM t1
CROSS JOIN t2
LEFT JOIN t3 ON t1.id = t3.id1 AND t2.id = t3.id2 AND t3.time > SUBDATE(NOW(),1)
GROUP BY t1.id, t2.id
ORDER BY t3hits,t2served,t2reported ASC LIMIT 10
我当前的表格需要12.45秒。
t1 is small, 20 records or so
t2 is 100k records or so
t3 is 100k records and growing
使用PHP通过http服务...
香港专业教育学院把索引到处都是，但它仍然很慢:)
任何帮助，将不胜感激。
谢谢！
这是解释和索引
并作为文本：
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL id1unique 50 NULL 13 Using index; Using temporary; Using filesort
1 SIMPLE t2 ALL NULL NULL NULL NULL 11652 Using join buffer (flat, BNL join)
1 SIMPLE t3 ref ids_index ids_index 8 id1,id2 1 Using where
Indexes
id1unique t1.id
ids_index id1,id2
为什么使用它的更多说明
t1 is a set of customers
t2 is a set of products
t3 has id of a customer and product and timestamp when it was purchased
我要为最近24小时内未购买的客户产品或最近24小时内最少购买的产品提供服务
那就是整个过程:)
最佳答案
我会在加入之前汇总。毕竟，您想知道每个id1 / id2的t3行数，因此加入“行数”：
SELECT
t1.name AS t1name,
t1.id AS t1id,
t2.name AS t2name,
t2.id AS t2id,
t2.surname AS t2surname,
t2.served AS t2served,
t2.reported AS t2reported,
COALESCE(t3agg.cnt, 0) AS t3hits
FROM t1
CROSS JOIN t2
LEFT JOIN
(
select id1, id2, count(*) as cnt
from t3
where t3.time > subdate(now(), 1)
group by id1, id2
) t3agg ON t1.id = t3agg.id1 AND t2.id = t3agg.id2
ORDER BY t3hits, t2served, t2reported
LIMIT 10;
您应该具有以下索引：
create index idx3 on t3(time, id1, id2);
该索引使DBMS可以快速查找最近24小时内相对较少的行，并立即使用id1和id2，而无需查找表中的行。
你甚至可以做这个
create index idx3 on t3(time, id1, id2, name);
因此，甚至不需要读取该表的名称。那应该是尽可能快的。
关于mysql - MySQL 3表交叉/左连接优化，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/53103857/