当我运行此查询时,平均花了1.2421秒,我认为这很慢,我在这些WHERE子句中的每个可能的列中添加了索引。因此,我可以做些改进来加快此查询的速度?包含最多数据的表是eav,其具有约111276行/记录
SELECT SQL_CALC_FOUND_ROWS eav.entid,
ent.entname
FROM eav,
ent,
catatt ca
WHERE eav.entid = ent.entid
AND ent.status = 'active'
AND eav.status = 'active'
AND eav.attid = ca.attid
AND ca.catid = 1
AND eav.catid = 1
AND ( ca.canviewby <= 6
|| ( ent.addedby = 87
AND canviewby <= 6 ) )
AND ( ( eav.attid = 13
AND ( `char` = '693fafba093bfa35118995860e340dce' ) )
OR ( eav.attid = 3
AND `double` = 6 )
OR ( eav.attid = 45
AND ( `int` = 191 ) ) )
GROUP BY eav.entid
HAVING Count(*) >= 3
我简化了您的查询以更好地理解它,从where子句中删除了不必要的情况,从而进行了查询计划。
因此,请检查此查询并注释结果,然后在我的回答下对其进行调试:
SELECT
SQL_CALC_FOUND_ROWS
eav.entid,
ent.entname
FROM
eav
INNER JOIN ent ON (eav.entid = ent.entid AND ent.status = 'active')
INNER JOIN catatt ON (eav.attid = catatt.attid AND catatt.catid = 1)
WHERE
eav.catid = 1 AND eav.status = 'active'
AND (catatt.canviewby <= 6 OR ent.addedby = 87)
AND
(
(eav.attid = 13 AND eav.`char` = '693fafba093bfa35118995860e340dce')
OR
(eav.attid = 3 AND eav.`double` = 6)
OR
(eav.attid = 45 AND eav.`int` = 191)
)
GROUP BY eav.entid
HAVING COUNT(eav.entid) > 2
+还我看到你已经很少UPDATE
-ing表(主要是插入到这些表中的数据) -所以要尽量使这些表的引擎是MyISAM
+从组合创建复合索引:attid, char
; attid, double
; attid, int
+查看mysql的配置并对其进行调整,以更好地查询缓存和使用内存
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句