CODEWITHSUNDEEP
mysqlsqlperformance
MrC
MrC

Reputation: 381

MySQL query running *extremely* slowly

I have a sql query that is running really slow and I am perplexed as to why. The query is:

SELECT DISTINCT(c.ID),c.* FROM `content` c 
  LEFT JOIN `content_meta` cm1 ON c.id =    cm1.content_id 
 WHERE 1=1 
   AND c.site_id IN (14) 
   AND c.type IN ('a','t') 
   AND c.status = 'visible' 
   AND (c.lock = 0 OR c.site_id = 14) 
   AND c.level = 0 
    OR 
       (
          (     c.site_id = 14 
            AND cm1.meta_key = 'g_id' 
            AND cm1.meta_value IN ('12','13','7')
          ) 
          OR 
          (     c.status = 'visible' 
            AND ( 
                    (c.type = 'topic' AND c.parent_id IN (628,633,624))
                )
          )
       ) 
 ORDER BY c.date_updated DESC LIMIT 20

The content table has about 1250 rows and the content meta table has about 3000 rows. This isn't a lot of data and I'm not quite sure what may be causing it to run so slow. Any thoughts/opinions would be greatly appreciated.

Thanks!

Upvotes: 1

Views: 104

Answers (2)

DRapp
DRapp

Reputation: 48179

It might have to do with your "OR" clause at the end... your up-front are being utilized by the indexes where possible, but then you throw this huge OR condition at the end that can be either one or the other. Not knowing more of the underlying content, I would adjust to have a UNION on the inside so each entity can utilize its own indexes, get you qualified CIDs, THEN join to final results.

select
      c2.*
   from
      ( select distinct
              c.ID
           from
              `content` c
           where
                  c.site_id in (14)
              and c.type in ('a', 't' )
              and c.status = 'visible'
              and c.lock in ( 0, 14 )
              and c.level = 0
        UNION
        select
              c.ID
           from
              `content` c
           where
                  c.status = 'visible'
              and c.type = 'topic'
              and c.parent_id in ( 628, 633, 624 )
        UNION
        select
              c.ID
           from
              `content` c
                 join `content_meta` cm1
                    on c.id = cm1.content_id
                   AND cm1.meta_key = 'g_id'
                   AND cm1.meta_value in ( '12', '13', '7' )
           where        
                  c.site_id = 14 ) PreQuery
      JOIN `content` c2
         on PreQuery.cID = c2.cID
   order by
      c2.date_updated desc
   limit
      20

I would ensure content table has an index on ( site_id, type, status ) another on (parent_id, type, status)

and the meta table, an index on ( content_id, meta_key, meta_value )

Upvotes: 0

Leandro Barreto
Leandro Barreto

Reputation: 361

Is you where clause correct? You are making a series of AND statements and later you are executing a OR.

Wouldn't the correct be something like:

AND (c.lock = 0 OR c.site_id = 14) 
AND (
        ( ... )
        OR
        ( ... )
   )

If it is indeed correct, you could think on changing the structure or treating the result in an script or procedure.

Upvotes: 1

Related Questions