MySql complicated join performance issue

Question

Please consider the following query

SELECT * FROM PC_SMS_OUTBOUND_MESSAGE AS OM  
JOIN MM_TEXTOUT_SERVICE AS TOS ON TOS.TEXTOUT_SERVICE_ID = OM.SERVICE_ID  
JOIN PC_SERVICE_NUMBER AS SN ON OM.TO_SERVICE_NUMBER_ID = SN.SERVICE_NUMBER_ID       
JOIN PC_SUBSCRIBER AS SUB ON SUB.SERVICE_NUMBER_ID = SN.SERVICE_NUMBER_ID  
JOIN MM_CONTACT CON ON CON.SUBSCRIBER_ID = SUB.SUBSCRIBER_ID 

--AND CON.MM_CLIENT_ID = 1

AND OM.CLIENT_ID= 1 
AND OM.CREATED>='2013-05-08 11:47:53' AND OM.CREATED<='2014-05-08 11:47:53'  
ORDER BY OM.SMS_OUTBOUND_MESSAGE_ID DESC LIMIT 50

To get the dataset I require I need to filter on the (commented out) CONTACTS client_id as well as the OUTBOUND_MESSAGES client_id but this is what changes the performance from milliseconds to tens of minutes.

Execution plan without "AND CON.MM_CLIENT_ID = 1":

id  select_type table   type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  OM  index   FK4E518EAA19F2EA2B,SERVICEID_IDX,CREATED_IDX,CLIENTID_IDX,CL_CR_ST_IDX,CL_CR_STYPE_ST_IDX,SID_TOSN_CL_CREATED_IDX   PRIMARY 8   NULL    6741    3732.00 Using where
1   SIMPLE  SUB ref PRIMARY,FKA1845E3459A7AEF   FKA1845E3459A7AEF   9   mmlive.OM.TO_SERVICE_NUMBER_ID  1   100.00  Using where
1   SIMPLE  SN  eq_ref  PRIMARY PRIMARY 8   mmlive.OM.TO_SERVICE_NUMBER_ID  1   100.00  Using where
1   SIMPLE  CON ref FK2BEC061CA525D30,SUB_CL_IDX    FK2BEC061CA525D30   8   mmlive.SUB.SUBSCRIBER_ID    1   100.00  
1   SIMPLE  TOS eq_ref  PRIMARY,FKDB3DF298AB3EF4E2  PRIMARY 8   mmlive.OM.SERVICE_ID    1   100.00

Execution plan with "AND CON.MM_CLIENT_ID = 1":

id  select_type table   type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  CON ref FK2BEC061CA525D30,FK2BEC06134399E2A,SUB_CL_IDX  FK2BEC06134399E2A   8   const   18306   100.00  Using temporary; Using filesort
1   SIMPLE  SUB eq_ref  PRIMARY,FKA1845E3459A7AEF   PRIMARY 8   mmlive.CON.SUBSCRIBER_ID    1   100.00  
1   SIMPLE  OM  ref FK4E518EAA19F2EA2B,SERVICEID_IDX,CREATED_IDX,CLIENTID_IDX,CL_CR_ST_IDX,CL_CR_STYPE_ST_IDX,SID_TOSN_CL_CREATED_IDX   FK4E518EAA19F2EA2B  9   mmlive.SUB.SERVICE_NUMBER_ID    3   100.00  Using where
1   SIMPLE  SN  eq_ref  PRIMARY PRIMARY 8   mmlive.SUB.SERVICE_NUMBER_ID    1   100.00  Using where
1   SIMPLE  TOS eq_ref  PRIMARY,FKDB3DF298AB3EF4E2  PRIMARY 8   mmlive.OM.SERVICE_ID    1   100.00

Any suggestions on how to format the above to make it a little easier on the eye would be good.

ID fields are primary keys. There are indexes on all joining columns.

You seem to be missing a `WHERE` clause, and the `OM` table. This is one of those cases where we need to see the EXPLAIN result and all of the indexes on the included tables. — Marcus Adams, May 13 '14 at 12:33
I don't think where is required here. AND seems to work fine without it when I test. Understood on the EXPLAIN result. Will have to wheel out the real query for that. — dre, May 13 '14 at 13:07
It's all in CAPS. Boo! All the same, I find doing this difficult, and doing it without DDLs is really difficult! — Strawberry, May 14 '14 at 16:32

Gordon Linoff · Answer 1 · 2014-05-13T20:05:51.907

0

You may be able to fix this problem by using a subquery:

JOIN (SELECT C.* FROM CONTACTS C WHERE C.USER_ID = 1) ON C.SUBSCRIBER_ID = SUB.ID

This will materialize the matching rows, which could have downstream effects on the query plan.

If this doesn't work, then edit your query and add:

The explain plans for both queries.
The indexes available on the table.

EDIT:

Can you try creating a composite index:

PC_SMS_OUTBOUND_MESSAGE(CLIENT_ID, CREATED, SERVICE_ID, TO_ SERVICE_ID, SMS_OUTBOUND_MESSAGE_ID);

This may change both query plans to start on the OM table with the appropriate filtering, hopefully making the results stable and good.

edited May 13 '14 at 20:05

answered May 13 '14 at 11:33

Gordon Linoff

1,242,037
58
646
786

Had Tried it, didn't work.. and I really thought it would! :( I guess it's because CONTACTS has millions of records. Will follow up with more detail. Explain will require using the "real world" query. – dre May 13 '14 at 12:08
@dre . . . Do you have an index on `CONTACTS(USER_ID)`? – Gordon Linoff May 13 '14 at 12:11
Yes, I have that index. – dre May 13 '14 at 13:08
With reference to the composite index you added in the edit, is there any strategy behind the ordering of the columns in the index? – dre May 15 '14 at 19:58
@dre . . . Yes. The first column is subject to an equality condition in the `where`; the second to an inequality, and the rest are there for the joins (so the original data page is not needed). – Gordon Linoff May 16 '14 at 03:25

score 0 · Accepted Answer · answered May 14 '14 at 16:28

I've solved the riddle! For my case anyway so I'll share.

This all came down to the join order changing once I added that extra clause, which you can clearly see in the execution plan. When the query is fast, the Outbound Messages are at the top of the plan but when slow (after adding the clause), the Contacts table is at the top. I think this means that the Outbound Messages index can no longer be utilised for the sorting which causes the dreaded;

 "Using temporary; Using filesort"

By simply adding STRAIGHT_JOIN keyword directly after the select I could force the execution plan to join in the order denoted directly by the query. Happy for anyone with a more intimate knowledge of this field to contradict any of the above in terms of what is actually happening but it definitely worked.

MySql complicated join performance issue

2 Answers2