MySQL 3表交叉/左连接优化

Question

我有三个表，Table1有id单元，Table2有id单元，Table3有id1单元，->对应于表1。Table3有id2单元，->对应于表2。id Table3有时间戳，因为只查找最后一天的数据

Table1和Table2有更多的数据需要返回。

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小时内购买最少的产品--这是整个过程:)

Answer 1

我会在加入之前聚集起来。毕竟，您想知道每个id1/id2 2的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);

所以这个表甚至不需要被读到这个名字。那应该是最快的了。