Re: MYSql Query - #2013 - Lost connection to MySQL server during query

[Peter Brawley]

Of course no-one would try to work with a query like that as unformatted text. Presumably you format it for readability in order to work on it, something like this?

SELECT GROUP_CONCAT(DISTINCT masterdetail.eventid) AS eventids
FROM tblsponsor_slave_eventprefrencesdetail                  userprefrence
JOIN tblsponsor_slave_sponsor_eventprefrence_category        userprefcategory    ON userprefrence.SponsorId = userprefcategory.SponsorId
JOIN tblsponsor_slave_sponsor_eventprefrence_locationtarget  userpreflocation    ON userprefrence.SponsorId = userpreflocation.SponsorId
JOIN tblsponsor_slave_sponsor_eventprefrence_type            userprefEventType   ON userprefrence.SponsorId = userprefEventType.SponsorId
JOIN tblsponsor_slave_sponsor_eventprefrence_audienceprofile userprefAudiProfile ON userprefrence.SponsorId = userprefAudiProfile.SponsorId
JOIN tblsponsor_slave_sponsor_eventprefrence_industry        userprefIndustry    ON userprefrence.SponsorId = userprefIndustry.SponsorId
JOIN tblsponsor_slave_eventprefrencesdetail                  userprefdetail      ON userprefrence.SponsorId=userprefdetail.SponsorId
JOIN tblsponsor_slave_sponsor_eventprefrence_audiencetype    userprefAudiType    ON userprefrence.SponsorId=userprefAudiType.SponsorId
JOIN tblsponsor_slave_eventprefrence_agegroup                userprefAgeGroup    ON userprefrence.SponsorId=userprefAgeGroup.SponsorId
JOIN tblevent_slave_event_address_city                       eventcity           ON userpreflocation.Sponsor_EventPrefrence_LocationTarget = eventcity.Event_Address_City
JOIN tblevent_slave_event_audienceprofile                    eventAudiProfile    ON userprefAudiProfile.Sponsor_EventPrefrence_AudienceProfile = eventAudiProfile.Event_AudienceProfile
JOIN tblevent_slave_event_industrycatered                    eventindustry       ON userprefIndustry.Sponsor_EventPrefrence_Industry = eventindustry.Event_IndustryCatered
JOIN tblevent_slave_agegroup                                 eventagegroup 
  ON userprefAgeGroup.Sponsor_AgeGroup_Type=eventagegroup.Event_AgeGroup_Type
  AND userprefAgeGroup.IsExist=eventagegroup.IsExist
JOIN tblevent_master_detail                                  masterdetail        ON eventcity.EventId = masterdetail.EventId
JOIN tblevent_slave_others                                   eventothers 
  ON masterdetail.EventId=eventothers.EventId
 AND masterdetail.Event_Category = userprefcategory.Sponsor_EventPrefrence_Category
 AND masterdetail.Event_Type=userprefEventType.Sponsor_EventPrefrence_Type
 AND eventothers.Event_Gender=userprefdetail.Sponsor_EventPrefrence_Gender
 AND eventothers.Event_TargetAudience=userprefAudiType.Sponsor_EventPrefrence_AudienceType
 AND eventothers.Event_Sponsor_Range_Min < userprefrence.Sponsor_EventPrefrence_Range_Max
WHERE userprefrence.SponsorId =164
  AND eventagegroup.IsExist=1
  AND masterdetail.Event_StartDateTime > '2015-08-27'
  AND masterdetail.Event_Category IN ("1","2", "3", "7", "9", "10", "11")
  AND eventcity.Event_Address_City IN ("1","3","54")
  AND masterdetail.Event_Type IN ("2", "3", "4", "5","7")
  AND eventindustry.Event_IndustryCatered IN ("13", "18")
  AND eventAudiProfile.Event_AudienceProfile IN ("1", "2", "10");

Why not do that for us poor readers too? :-)

Sixteen tables, eight Where conditions. The query will need intelligent indexing and MySQL tuning. For starters can we assume all tables are InnoDB? Let's see the results of Explain Extended on the query, and let's see innodb buffer pool settings (see https://dev.mysql.com/doc/refman/5.6/en/innodb-buffer-pool.html).