William William - 1 month ago 21
SQL Question

Increase MySql query performance

When I run this query, it took an average of 1.2421 seconds, which I think is slow, I have added indexing to every single possible column in those WHERE clause. So anymore improvement that I can do to speed up this query? The table that contains most data is the eav which have around 111276 rows/records

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


EXPLAIN output
EXPLAIN OUTPUT

catatt table index
enter image description here

eav table indexenter image description here

ent table index
enter image description here

Answer Source

I have simplified Your query to understand it better, removed unnecessary case from where clause, made query planning.

So check this query and put to comment results and let's debug it under my answer:

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 



+ also I see You've rarely UPDATE-ing tables (data mostly inserted to these tables) - so try to make these table's engine to be MyISAM
+ create compound indexes from combinations of: attid, char ; attid, double ; attid, int
+ take a look at mysql's configuration and tune it for better query caching and memory usage