Search code examples
mysqlsqldatabaseperformanceentity-attribute-value

Increase MySql EAV 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

Solution

  • 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