Page 2 of 2 First 12
  • Jump to page:
    #16
  1. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,803
    Rep Power
    1959
    Looking at your table definition, it says that fieldID/field_id is an integer. Then you should remove " " around the numbers.

    Did you try the EXPLAIN SELECT on your query? on our examples/suggestions?
  2. #17
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    8
    Rep Power
    0
    Hey, I modify the query slightly to make sure I was getting real results against the contacts. I only changed what it was searching for:

    SELECT c.firstName , c.physicalStreet , c.officePhone , c.email1 , c.middleName , c.homePhone , c.email2 , c.physicalCity ,
    c.physicalState , c.lastName , c.mobilePhone , c.physicalZip , c.physicalCountry , c.dob , c.gender , c.employer ,
    c.priority , cd1.string AS custom , cd2.string AS custom_1181
    FROM contacts c
    LEFT JOIN contact_field_data cd1 ON cd1.contact_id = c.contactID AND cd1.field_id = 1181 AND cd1.string = "46-55"
    LEFT JOIN contact_field_data cd2 ON cd2.contact_id = c.contactID AND cd2.field_id = 982 AND cd2.string = "9"
    JOIN contactgroupactive cga ON cga.accountid = 1
    WHERE c.accountid = 1
    AND firstName LIKE "%da%"
    AND lastName LIKE "%an%"
    AND cga.groupID IN (4,16,27)
    GROUP BY c.contactid

    The explain gave this:
    -----------------------
    id: 1
    select_type: SIMPLE
    table: cga
    type: index
    possible_keys: NULL
    key: PRIMARY
    key_len: 12
    ref: NULL
    rows: 243
    Extra: Using where; Using index; Using temporary; Using filesort
    -----------------------
    id: 1
    select_type: SIMPLE
    table: c
    type: ALL
    possible_keys: NULL
    key: PRIMARY
    key_len: NULL
    ref: NULL
    rows: 155569
    Extra: Using where; Using join buffer
    -----------------------
    id: 1
    select_type: SIMPLE
    table: cd1
    type: ref
    possible_keys: contact_id
    key: contact_id
    key_len: 4
    ref: c.contactID
    rows: 17
    Extra:
    -----------------------
    id: 1
    select_type: SIMPLE
    table: cd2
    type: ref
    possible_keys: contact_id
    key: contact_id
    key_len: 4
    ref: c.contactID
    rows: 17
    Extra:
  4. #18
  5. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,803
    Rep Power
    1959
    This part looks strange:
    Code:
    JOIN contactgroupactive cga ON cga.accountid = 1
    WHERE c.accountid = 1
    Which table do you want to join with cga?
    Isn't cga.accountid a FK of c.accountid? then you don't need to have both fields in a condition when using INNER JOIN.
    Last edited by MrFujin; February 10th, 2014 at 12:24 PM.
  6. #19
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    8
    Rep Power
    0
    cool, I will add an either/or in the code because there is an option do to a search without looking for anything in contactgroupactive

    Unfort. it still brings the server to its knees.
  8. #20
  9. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,803
    Rep Power
    1959
    Based on the EXPLAIN you posted, the only "issue" seems to be related to cga table.
    Don't know, form your post, if you tried a run without this table?

    Also notice that your GROUP BY is valid for MySQL, but not for standard SQL.

    What if you try some smaller statements.
    For example:
    sql Code:
     
    SELECT c.contactid
         , c.firstName
         , c.lastName 
    	 , cfd.string AS custom 
    FROM contacts c
    LEFT JOIN contact_field_data cfd ON cfd.contact_id = c.contactID


    Alternative:
    sql Code:
     
    SELECT c.firstName
         , c.lastName 
    	 , GROUP_CONCAT(cfd.string) AS custom 
    FROM contacts c
    LEFT JOIN contact_field_data cfd ON cfd.contact_id = c.contactID
    GROUP BY c.contactid


    Eventually try to run EXPLAIN SELECT on both of them.

    Those should not bring down the server to its knees.
Page 2 of 2 First 12
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo