Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    8
    Rep Power
    0

    Query crawling because of to many joins


    I need help with an sql statement. I am trying to create a search query that looks up contacts. There are about 1 million contacts. I hope this is clear.

    My tables looks like this:

    contacts: (these are ‘standard’ fields)
    accountID - int (indexed) - id for users/account
    contactID - int (indexed) - id for contact - p.key
    firstname - varchar(20)
    lastname - varchar(20)
    (and has about 30 more)

    customfields: (these are the ID’s and titles for custom fields - these are not included in this search - just added to make it clearerier)
    fieldID - int (indexed) - p.key
    accountID - int (indexed) - account ID
    fieldname - varchar(20) - visible field name
    listOrder - int - order the custom fields are listed in

    customfielddata: (data for custom fields)
    dataID - int - p.key for data
    contactID - int (indexed) - contact’s ID
    fieldID - int (indexed) - field’s ID
    strValue - varchar(200) - string value for this custom field

    contactGroups: (grouping table for contacts)
    groupID - int - group ID - p.key
    groupName - varchar(20) - visible name of group
    accountID - int (indexed) - account ID reference

    contactGroupLookup: (reference table for contact and groups)
    lookupID - int - p.key for table
    groupID - int (indexed) - contactGroups lookup
    contactID - int (indexed) - contacts lookup

    OK, so I need to do a search like this:
    Search in contacts for:
    contacts.firstname like “%john%”
    AND contacts.lastname like “%doe%”
    AND customfielddata.dataID = 1 AND customfielddata.dataID.strValue = “Outside”
    AND customfielddata.dataID = 2 AND customfielddata.dataID.strValue = “Male”
    AND customfielddata.dataID = 3 AND customfielddata.dataID.strValue = “Green”
    AND contactGroupLookup.groupID IN (1,2,3)


    I am building it right now by doing a series of joins, but I know there is a better way. This query is possible of bring the server to a halt!

    SELECT c.firstname, c.lastname, cd1.strValue AS custom_1, cd2.strValue AS custom_2, cd3.strValue AS custom_3, cd4.strValue AS custom_4, cd5.strValue AS custom_5, cd6.strValue AS custom_6, cd7.strValue AS custom_7, cd8.strValue AS custom_8, cd9.strValue AS custom_9
    FROM contacts c
    RIGHT JOIN customfielddata cd1 ON cd1.contactID = c.contactID AND cd1.fieldID = "42" AND cd1.strValue = “Outside”
    RIGHT JOIN customfielddata cd2 ON cd2.contactID = c.contactID AND cd2.fieldID = "42" AND cd2.strValue = “Male”
    RIGHT JOIN customfielddata cd3 ON cd3.contactID = c.contactID AND cd3.fieldID = "42" AND cd3.strValue = “Green”
    RIGHT JOIN customfielddata cd4 ON cd4.contactID = c.contactID AND cd4.fieldID = "42"
    RIGHT JOIN customfielddata cd5 ON cd5.contactID = c.contactID AND cd5.fieldID = "42"
    RIGHT JOIN customfielddata cd6 ON cd6.contactID = c.contactID AND cd6.fieldID = "42"
    RIGHT JOIN customfielddata cd7 ON cd7.contactID = c.contactID AND cd7.fieldID = "42"
    RIGHT JOIN customfielddata cd8 ON cd8.contactID = c.contactID AND cd8.fieldID = "42"
    RIGHT JOIN customfielddata cd9 ON cd9.contactID = c.contactID AND cd9.fieldID = "42"
    JOIN contactGroupLookup clu ON clu.accountid = 1
    WHERE c.accountid = 1 AND c.firstname LIKE “%john%”
    AND c.lastname LIKE “%doe%”
    AND cga.groupID IN (6,100)
    GROUP BY c.contactid


    I really hope this makes sense and I did not give to much info.
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,663
    Rep Power
    1958
    You want to use LEFT JOIN instead of RIGHT JOIN.


    What is your thought with all those joins?
    Join number 5 will just be a repeat of join number 4.
    If you want a field to contain specific values, you should be able to use use the IN condition:
    Code:
    RIGHT JOIN customfielddata cd1 ON cd1.contactID = c.contactID AND cd1.fieldID = "42" AND cd1.strValue IN ('Outside','Male','Green')
    This one also looks wrong, as it doesn't have any condition towards another table:
    Code:
    JOIN contactGroupLookup clu ON clu.accountid = 1
    You have explained what you tried, but not the result you want.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    8
    Rep Power
    0
    Hey, I typed it wrong, I was pulling out of the overall system and trying to make it make more sense. My query is more like this (with changes in red). The fieldID's are all suppose to be different.

    SELECT c.firstname, c.lastname, cd1.strValue AS custom_1, cd2.strValue AS custom_2, cd3.strValue AS custom_3, cd4.strValue AS custom_4, cd5.strValue AS custom_5, cd6.strValue AS custom_6, cd7.strValue AS custom_7, cd8.strValue AS custom_8, cd9.strValue AS custom_9
    FROM contacts c
    RIGHT JOIN customfielddata cd1 ON cd1.contactID = c.contactID AND cd1.fieldID = "42" AND cd1.strValue = “Outside”
    RIGHT JOIN customfielddata cd2 ON cd2.contactID = c.contactID AND cd2.fieldID = "43" AND cd2.strValue = “Male”
    RIGHT JOIN customfielddata cd3 ON cd3.contactID = c.contactID AND cd3.fieldID = "44" AND cd3.strValue = “Green”
    RIGHT JOIN customfielddata cd4 ON cd4.contactID = c.contactID AND cd4.fieldID = "45"
    RIGHT JOIN customfielddata cd5 ON cd5.contactID = c.contactID AND cd5.fieldID = "46"
    RIGHT JOIN customfielddata cd6 ON cd6.contactID = c.contactID AND cd6.fieldID = "47"
    RIGHT JOIN customfielddata cd7 ON cd7.contactID = c.contactID AND cd7.fieldID = "48"
    RIGHT JOIN customfielddata cd8 ON cd8.contactID = c.contactID AND cd8.fieldID = "48"
    RIGHT JOIN customfielddata cd9 ON cd9.contactID = c.contactID AND cd9.fieldID = "49"
    JOIN contactGroupLookup clu ON clu.accountid = c.accountID
    WHERE c.accountid = 1 AND c.firstname LIKE “%john%”
    AND c.lastname LIKE “%doe%”
    AND cga.groupID IN (6,100)
    AND c.accountID = 1
    GROUP BY c.contactid
  6. #4
  7. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,663
    Rep Power
    1958
    I think you want to use LEFT JOIN and not RIGHT JOIN, i.e.. get all contacts (LEFT table) and then include/join the values from customfielddata where available.
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by ignitenc
    AND cga.groupID IN (6,100)
    this will cause a syntax error, because your query does not have a cga table

    more editorializing?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    8
    Rep Power
    0
    Thank you, yes, I it suppose to be like below. If I get my edited version correct it will make it easier to ask for help! Thanks for pointing that error out.

    SELECT c.firstname, c.lastname, cd1.strValue AS custom_1, cd2.strValue AS custom_2, cd3.strValue AS custom_3, cd4.strValue AS custom_4, cd5.strValue AS custom_5, cd6.strValue AS custom_6, cd7.strValue AS custom_7, cd8.strValue AS custom_8, cd9.strValue AS custom_9
    FROM contacts c
    RIGHT JOIN customfielddata cd1 ON cd1.contactID = c.contactID AND cd1.fieldID = "42" AND cd1.strValue = “Outside”
    RIGHT JOIN customfielddata cd2 ON cd2.contactID = c.contactID AND cd2.fieldID = "42" AND cd2.strValue = “Male”
    RIGHT JOIN customfielddata cd3 ON cd3.contactID = c.contactID AND cd3.fieldID = "42" AND cd3.strValue = “Green”
    RIGHT JOIN customfielddata cd4 ON cd4.contactID = c.contactID AND cd4.fieldID = "42"
    RIGHT JOIN customfielddata cd5 ON cd5.contactID = c.contactID AND cd5.fieldID = "42"
    RIGHT JOIN customfielddata cd6 ON cd6.contactID = c.contactID AND cd6.fieldID = "42"
    RIGHT JOIN customfielddata cd7 ON cd7.contactID = c.contactID AND cd7.fieldID = "42"
    RIGHT JOIN customfielddata cd8 ON cd8.contactID = c.contactID AND cd8.fieldID = "42"
    RIGHT JOIN customfielddata cd9 ON cd9.contactID = c.contactID AND cd9.fieldID = "42"
    JOIN contactGroupLookup clu ON clu.accountid = 1
    WHERE c.accountid = 1 AND c.firstname LIKE “%john%”
    AND c.lastname LIKE “%doe%”
    AND clu.groupID IN (6,100)
    GROUP BY c.contactid
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    those have to be LEFT, not RIGHT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,450
    Rep Power
    1751
    Your "cd?.fieldID =" conditions have gone back to all being a check for 42 (which is, of course, a answer to most th8ings .. )
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    8
    Rep Power
    0
    Sorry! Let's try this:

    SELECT c.firstname, c.lastname, cd1.strValue AS custom_1, cd2.strValue AS custom_2, cd3.strValue AS custom_3, cd4.strValue AS custom_4, cd5.strValue AS custom_5, cd6.strValue AS custom_6, cd7.strValue AS custom_7, cd8.strValue AS custom_8, cd9.strValue AS custom_9
    FROM contacts c
    RIGHT JOIN customfielddata cd1 ON cd1.contactID = c.contactID AND cd1.fieldID = "42" AND cd1.strValue = “Outside”
    RIGHT JOIN customfielddata cd2 ON cd2.contactID = c.contactID AND cd2.fieldID = "43" AND cd2.strValue = “Male”
    RIGHT JOIN customfielddata cd3 ON cd3.contactID = c.contactID AND cd3.fieldID = "44" AND cd3.strValue = “Green”
    RIGHT JOIN customfielddata cd4 ON cd4.contactID = c.contactID AND cd4.fieldID = "45"
    RIGHT JOIN customfielddata cd5 ON cd5.contactID = c.contactID AND cd5.fieldID = "46"
    RIGHT JOIN customfielddata cd6 ON cd6.contactID = c.contactID AND cd6.fieldID = "47"
    RIGHT JOIN customfielddata cd7 ON cd7.contactID = c.contactID AND cd7.fieldID = "48"
    RIGHT JOIN customfielddata cd8 ON cd8.contactID = c.contactID AND cd8.fieldID = "48"
    RIGHT JOIN customfielddata cd9 ON cd9.contactID = c.contactID AND cd9.fieldID = "49"
    JOIN contactGroupLookup clu ON clu.accountid = c.accountID
    WHERE c.accountid = 1 AND c.firstname LIKE “%john%”
    AND c.lastname LIKE “%doe%”
    AND clu.groupID IN (6,100)
    AND c.accountID = 1
    GROUP BY c.contactid
  18. #10
  19. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,663
    Rep Power
    1958
    You forgot to change RIGHT JOIN to LEFT JOIN:
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    8
    Rep Power
    0
    I changed them out and the database keeps dropping the connection or timing out after trying to run it for about 1 minute. The final query for some clients can be longer than my sample. Here is a true query right from the system (with lefts)

    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_971 , cd2.string AS custom_972 , cd3.string AS custom_973 , cd4.string AS custom_974 ,
    cd5.string AS custom_977 , cd6.string AS custom_976 , cd7.string AS custom_978 , cd8.string AS custom_979 ,
    cd9.string AS custom_980 , cd10.string AS custom_981 , cd11.string AS custom_982 , cd12.string AS custom_983 ,
    cd13.string AS custom_984 , cd14.string AS custom_985 , cd15.string AS custom_986 , cd16.string AS custom_987 ,
    cd17.string AS custom_988 , cd18.string AS custom_989 , cd19.string AS custom_990 , cd20.string AS custom_991 ,
    cd21.string AS custom_992 , cd22.string AS custom_993 , cd23.string AS custom_994 , cd24.string AS custom_1180 ,
    cd25.string AS custom_1181 , cd26.string AS custom_1186 , cd27.string AS custom_1187
    FROM contacts c
    LEFT JOIN contact_field_data cd1 ON cd1.contact_id = c.contactID AND cd1.field_id = "971"
    LEFT JOIN contact_field_data cd2 ON cd2.contact_id = c.contactID AND cd2.field_id = "972"
    LEFT JOIN contact_field_data cd3 ON cd3.contact_id = c.contactID AND cd3.field_id = "973"
    LEFT JOIN contact_field_data cd4 ON cd4.contact_id = c.contactID AND cd4.field_id = "974"
    LEFT JOIN contact_field_data cd5 ON cd5.contact_id = c.contactID AND cd5.field_id = "977"
    LEFT JOIN contact_field_data cd6 ON cd6.contact_id = c.contactID AND cd6.field_id = "976"
    LEFT JOIN contact_field_data cd7 ON cd7.contact_id = c.contactID AND cd7.field_id = "978"
    LEFT JOIN contact_field_data cd8 ON cd8.contact_id = c.contactID AND cd8.field_id = "979"
    LEFT JOIN contact_field_data cd9 ON cd9.contact_id = c.contactID AND cd9.field_id = "980"
    LEFT JOIN contact_field_data cd10 ON cd10.contact_id = c.contactID AND cd10.field_id = "981"
    LEFT JOIN contact_field_data cd11 ON cd11.contact_id = c.contactID AND cd11.field_id = "982"
    LEFT JOIN contact_field_data cd12 ON cd12.contact_id = c.contactID AND cd12.field_id = "983"
    LEFT JOIN contact_field_data cd13 ON cd13.contact_id = c.contactID AND cd13.field_id = "984"
    LEFT JOIN contact_field_data cd14 ON cd14.contact_id = c.contactID AND cd14.field_id = "985"
    LEFT JOIN contact_field_data cd15 ON cd15.contact_id = c.contactID AND cd15.field_id = "986"
    LEFT JOIN contact_field_data cd16 ON cd16.contact_id = c.contactID AND cd16.field_id = "987"
    LEFT JOIN contact_field_data cd17 ON cd17.contact_id = c.contactID AND cd17.field_id = "988"
    LEFT JOIN contact_field_data cd18 ON cd18.contact_id = c.contactID AND cd18.field_id = "989"
    LEFT JOIN contact_field_data cd19 ON cd19.contact_id = c.contactID AND cd19.field_id = "990"
    LEFT JOIN contact_field_data cd20 ON cd20.contact_id = c.contactID AND cd20.field_id = "991"
    LEFT JOIN contact_field_data cd21 ON cd21.contact_id = c.contactID AND cd21.field_id = "992"
    LEFT JOIN contact_field_data cd22 ON cd22.contact_id = c.contactID AND cd22.field_id = "993"
    LEFT JOIN contact_field_data cd23 ON cd23.contact_id = c.contactID AND cd23.field_id = "994"
    LEFT JOIN contact_field_data cd24 ON cd24.contact_id = c.contactID AND cd24.field_id = "1180"
    LEFT JOIN contact_field_data cd25 ON cd25.contact_id = c.contactID AND cd25.field_id = "1181" AND cd25.string = "46-55"
    LEFT JOIN contact_field_data cd26 ON cd26.contact_id = c.contactID AND cd26.field_id = "1186"
    LEFT JOIN contact_field_data cd27 ON cd27.contact_id = c.contactID AND cd27.field_id = "1187"
    JOIN contactgroupactive cga ON cga.accountid = 1
    WHERE c.accountid = 1
    AND firstName LIKE "%john%"
    AND lastName LIKE "%d%"
    AND cga.groupID IN (4,16,27)
    GROUP BY c.contactid
  22. #12
  23. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,663
    Rep Power
    1958
    What result do you get if you try to run it using EXPLAIN SELECT ...

    What result do you get if you try a join like this:
    sql Code:
     
    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
    FROM contacts c
    LEFT JOIN contact_field_data cd1 ON cd1.contact_id = c.contactID AND cd1.field_id IN ("971","972")
    LEFT JOIN contact_field_data cd2 ON cd25.contact_id = c.contactID AND cd2.field_id = "1181" AND cd2.string = "46-55"
    JOIN contactgroupactive cga ON cga.accountid = 1
    WHERE c.accountid = 1
    AND firstName LIKE "%john%"
    AND lastName LIKE "%d%"
    AND cga.groupID IN (4,16,27)
    GROUP BY c.contactid

    Please notice I didn't include all field_id's.

    Just wondering why field_id is not an integer?
  24. #13
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    i don't have any advice to offer on making that query more efficient, sorry

    perhaps this thread could serve as an example of the dangers in designing a schema with "custom" columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  26. #14
  27. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    Are you sure you can't rewrite the surrounding code so that you can utilize rows instead of columns?

    Anyway here's suggestion for solution for you that might run faster since you only join once with the contact_field_data table but then you will have to group the result and filter out the corresponding values for each column in the SELECT part:
    Code:
    SELECT
      MAX(c.firstName)
      , MAX(c.physicalStreet)
      , ... 
      , MAX(CASE WHEN cd.field_id = "971" THEN cd.string END) AS custom_971
      , MAX(CASE WHEN cd.field_id = "972" THEN cd.string END) AS custom_972
    , ...
    FROM contacts c
    JOIN contactgroupactive cga ON cga.accountid = c.accountid
    LEFT JOIN contact_field_data cd ON cd.contact_id = c.contactID
    ...
    GROUP BY c.contactid
    So what happens:
    1.
    You only join once with the contact_field_data so you get a lot or rows.

    2.
    You GROUP BY c.contactid (since I'm assuming that that is the primary key all firstname, physicaladdress, etc, columns will be identical so I took the shortcut with MAX() around them to filter out just one record

    3.
    For the specific contact_field_data you first have to filter the rows of values with a "CASE WHEN cd.field_id = "971" THEN cd.string END" that will give you a table something like this:
    ''
    ''
    ''
    'SomeString'
    ''
    ''
    And then you use MAX() around this table to retrieve the actual string you want.

    4.
    Your placement and join condition on the contactgroupactive table is odd.
    Since you are performing a normal join only dependent on the original contact table it should be placed directly under contact like in my example and since I'm assuming that cga.accountid = c.accountid you should write it that way.
    Spreading out the constant value in the query does not make the query faster only harder to read and maintain.

    Good luck!
    /Stefan
  28. #15
  29. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    8
    Rep Power
    0
    Sr and MrFujin - thanks for taking time to look at this query!

    I tried MrFunin's suggestion but still having issues. I am going to

    MrFujin - I tired what you suggested and you are right - still causing timeout issues.

    I am going to redo this and drop the custom tables. I think I will just include the custom fields inside of the contacts table and set a limit of only 50 custom fields, so the contacts will just have custom1, etc.

    What do you think? I really wanted to have an unlimited number of custom fields, but in reality 50 is enough for most people.
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo