LEFT JOIN Performance

[chris.olver]

Hi,

Via another topic I learnt how to get around a few issues when LEFT JOINs do some weird things by doing a sub query in the select part of a left join. Example:

 

SELECT * , l.`LicID` AS LicenceID, u.`name` As SalesName, SUM(i.ConvertPounds) As LicenceTotal, c.`name` As CampaignName
FROM implementation.licensing l
LEFT JOIN implementation.invoice i ON l.LicID=i.LicenseID
LEFT JOIN implementation.subscribed s ON s.licID=l.LicID
LEFT JOIN implementation.methods m ON s.methodID=m.metID
LEFT JOIN implementation.campaign c ON c.campID=m.campID
LEFT JOIN implementation.users u ON u.userID=l.SalesPerson
LEFT OUTER JOIN (
  SELECT a.LicenseID,
  GROUP_CONCAT(DISTINCT(a.ConID),',',a.`Description`,',',a.`DueDate` SEPARATOR '|') As ContactRequests
  FROM licensing li
  LEFT JOIN contact a
  ON a.LicenseID=li.LicID 
  GROUP BY LicenseID) ip
ON l.LicID = ip.LicenseID
WHERE l.`ProfileID` = '371206' AND l.LicID != 0
GROUP BY l.`LicID`
ORDER BY i.InvCreateDate;

 

Now I had to do a different LEFT JOIN on the contacts table because if there was multiple contact requests, I would find: SUM(i.ConvertPounds) would provide the wrong result because it would count a distinct i.ConvertPounds record multiple times during the SUM() (it should only count that field once per record).. The amount of times would depend on the amount of contacts requests.

 

While the above now works and performance is OK, doing an "EXPLAIN" on the query shows the subquery LEFT JOIN is pretty harsh. How can I put some form of WHERE stipulation in the sub query? I thought of trying to put a where column in the subquery so I wouldn't have to a left join on every single record in the licensing table.

 

.....................
LEFT OUTER JOIN (
  SELECT a.LicenseID,
  GROUP_CONCAT(DISTINCT(a.ConID),',',a.`Description`,',',a.`DueDate` SEPARATOR '|') As ContactRequests
  FROM licensing li
  LEFT JOIN contact a
  ON a.LicenseID=li.LicID WHERE LicenceID=a.LicenseID
  GROUP BY LicenseID) ip
ON l.LicID = ip.LicenseID
...............................................................

 

But this won't work because it doesn't know the LicenceID or l.LicID column exists. Does anyone have any ideas or feedback on how I can either improve the subquery performance or remove the subquery all together without duplicating the SUM(i.ConvertPounds) result. I have indexes on all the columns used within the search

 

Many thanks.

[fenway]

Show us the EXPLAIN output.

[chris.olver]

1, 'PRIMARY', 'l', 'ref', 'PRIMARY,Profile', 'Profile', '4', 'const', 1, 'Using where; Using temporary; Using filesort'
1, 'PRIMARY', 'i', 'ref', 'Licenses', 'Licenses', '4', 'implementation.l.LicID', 1, ''
1, 'PRIMARY', 's', 'ref', 'LicLink', 'LicLink', '4', 'implementation.l.LicID', 1, 'Using index'
1, 'PRIMARY', 'm', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'implementation.s.methodID', 1, ''
1, 'PRIMARY', 'c', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'implementation.m.campID', 1, ''
1, 'PRIMARY', 'u', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'implementation.l.SalesPerson', 1, ''
1, 'PRIMARY', '<derived2>', 'ALL', '', '', '', '', 5261, ''
2, 'DERIVED', 'li', 'index', '', 'PRIMARY', '4', '', 4891, 'Using index; Using temporary; Using filesort'
2, 'DERIVED', 'a', 'ref', 'LicenseID', 'LicenseID', '4', 'implementation.li.LicID', 1, ''

[fenway]

What's the perfomance of that subquery alone? And why all of hte left JOINs?