MySql LEFT JOIN Takes a Long Time - 已添加索引

Question

我使用以下查询

SELECT a.job_number as f31jn, b.job_number as f32jn
FROM 
f31 a LEFT JOIN f32 b 
ON (a.job_number = b.job_number AND a.date_audit = b.date_audit)
WHERE date_format(a.date_audit, '%b %Y') = 'Dec 2012'
GROUP BY a.job_number, a.ref_id, b.job_number, b.ref_id

上述查询大约需要 160 秒才能完成。

我已将索引添加到以下列:

f31 - job_number, date_audit (separate indexes)
f32 - job_number, date_audit (separate indexes)

EXPLAIN 以上查询结果为

编辑

将查询更改为

SELECT a.job_number as f31jn, b.job_number as f32jn
FROM 
f31 a LEFT JOIN f32 b 
ON (a.job_number = b.job_number AND a.date_audit = b.date_audit)
WHERE a.date_audit >= '2012-12-01' AND a.date_audit < '2013-01-01'
GROUP BY a.job_number, a.ref_id, b.job_number, b.ref_id

执行时间仍然没有下降。它保持在 150 到 160 秒的相同近似范围内。

EXPLAIN 现在显示为

对于表a，它仍在搜索90%的记录(总记录约为68k)。

Answer 1

MySQL 无法使用索引来帮助处理 date_format(a.date_audit, '%b %Y') = 'Dec 2012'。

您是否有关于 job_number 和 date_audit 的单独索引或一个组合索引。如果它是一个组合索引，那么 MySQL 不能单独将它用于任何形式的 date_audit 查询。要么单独在 date_audit 上创建索引，要么先创建 date_audit 和 job_number 的组合索引。

然后转换 WHERE 子句以将 date_audit 与实际日期值进行比较。

这有点摸不着头脑，但值得一试:

首先，计时:

SELECT * from f31 a 
WHERE a.date_audit >= '2012-12-01' 
  AND a.date_audit < '2013-01-01'

应该很快吧。如果不是，您需要验证您的索引和 date_audit 列的类型(DATE、DATETIME 或 TIMESTAMP)。

然后:

SELECT DISTINCT a.job_number as f31jn, b.job_number as f32jn
FROM 
  (SELECT * from f31 aa 
   WHERE aa.date_audit >= '2012-12-01' 
     AND aa.date_audit < '2013-01-01') as a    
LEFT JOIN f32 b 
ON (a.job_number = b.job_number AND a.date_audit = b.date_audit)

如果这还不能解决问题，那么请设置一个 SQL Fiddle带有架构和至少几条测试数据。