How can I optimize this mysql select + multiple joins query?

Question

I'm running this query but it's very slow. The OR clause on the last left join is what bogs it down, but I can't figure out another way to structure the query in order to obtain the results I'm looking for:

SELECT *,
       DATE_FORMAT(tbl1.pub, '%m/%d/%Y') AS pub_a,
       CONCAT(tbl1.code1, tbl1.code2) AS code_a
FROM (tbl1
      INNER JOIN tbl2 ON tbl1.pkid=tbl2.fkid
      LEFT JOIN tbl3 ON tbl1.pkid=tbl3.fkid)
LEFT JOIN tbl4 ON tbl1.person=tbl4.code OR tbl3.person=tbl4.code
WHERE tbl1.subcat != ''
  AND tbl1.pub < '2014-01-15 13:20:23'
  AND tbl1.exp > '2014-01-15 13:20:23'
ORDER BY tbl1.pub DESC 
LIMIT 0, 50

What we really love is when you write it all out on one line like that. — Strawberry
– Strawberry, Commented Jan 15, 2014 at 21:42
I personally love having to figure out the CREATE TABLE schema and SHOW INDEXES FROM. — bishop
– bishop, Commented Jan 15, 2014 at 21:44

Gordon Linoff · Accepted Answer · 2014-01-15 22:02:08Z

Unfortunately, optimizing or in an on clause is not easy. You could split this into two queries, combined with a union:

select *
from ((SELECT *,
              DATE_FORMAT(tbl1.pub, '%m/%d/%Y') AS pub_a,
              CONCAT(tbl1.code1, tbl1.code2) AS code_a
        FROM (tbl1
              INNER JOIN tbl2 ON tbl1.pkid=tbl2.fkid
              LEFT JOIN tbl3 ON tbl1.pkid=tbl3.fkid
              )
              LEFT JOIN tbl4 ON tbl3.person=tbl4.code
        WHERE tbl1.subcat != ''
          AND tbl1.pub < '2014-01-15 13:20:23'
          AND tbl1.exp > '2014-01-15 13:20:23'
        ORDER BY tbl1.pub DESC 
        LIMIT 0, 50
       ) union
       (SELECT *,
              DATE_FORMAT(tbl1.pub, '%m/%d/%Y') AS pub_a,
              CONCAT(tbl1.code1, tbl1.code2) AS code_a
        FROM (tbl1
              INNER JOIN tbl2 ON tbl1.pkid=tbl2.fkid
              LEFT JOIN tbl3 ON tbl1.pkid=tbl3.fkid
              )
              LEFT JOIN tbl4 ON tbl1.person=tbl4.code
        WHERE tbl1.subcat != ''
          AND tbl1.pub < '2014-01-15 13:20:23'
          AND tbl1.exp > '2014-01-15 13:20:23'
        ORDER BY tbl1.pub DESC 
        LIMIT 0, 50
       )
      ) t
order by pub desc
limit 0, 50

D'Arcy Rittich · Accepted Answer · 2014-01-15 21:45:36Z

1

This is a long shot, but try changing

left join tbl4 on tbl1.person = tbl4.code
    or tbl3.person = tbl4.code

to

left join tbl4 on tbl4.code in (tbl1.person, tbl3.person)

answered Jan 15, 2014 at 21:45

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

1 Comment

user3200064 Over a year ago

Thanks, that sped it up a little.

Collectives™ on Stack Overflow

How can I optimize this mysql select + multiple joins query?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related