Why my mysql MULTIPLE JOINS query is so slow after adopting all optimizations ?

1

Say you have a query like

SELECT
    t1.c1,
    t2.c2,
    ... more fields from joins ...
FROM t1
LEFT JOIN t2 ON t1.id = t2.id
... more joins ...
GROUP BY t1.field
WHERE ...

And you're having performance problems and you think you've covered all the common optimizations for this type of query, like defining indexes on the columns in your ON, GROUP BY, WHERE etc.. but it's still really slow.

rgvcorley

출처 mysql join group-by query-optimization
  • Thats because your db design and structure, we need to see what is happening in there, we need to know at least the "show table " and "explain query" command - jcho360
  • @jcho360 I answered my own question - see below, this was just in case anyone else had the same problem and didn't remember to check that all their joins were tables and not views! - rgvcorley
  • "I'm glad that you solved, anyway take a look to the explain command, he could tell you that you were making a join to a view, good luck - jcho360
  • Yeah explain didn't tell me I was joining to a view - I had already done explain and created indexes so all my joins were of type eq_ref so I was rather confused as to why it was still so slow until I realized! - rgvcorley

1 답변

1

Check that all of your joins are to actual tables and not views!

I know this is rather stating the obvious, but it stumped be for a while because I has just forgotten that one of the joins was to a view.

This is a question that I didn't get round to asking before I found the answer myself, but I wanted to post to help anyone else with the same issue as it wasn't mentioned in any of the other posts about optimizing GROUP BY queries with JOINs. I'm not sure if this is accepted on SO, but I have also added to the accepted answer on this post, so I won't be offended if the administrators want to delete this post.

  • Smart answer for "Check that all of your joins are to actual tables and not views!". (y) - NullPointer

Linked

Related

Latest