Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2015
    Posts
    4

    Unanswered: Performance Issue Help: String search in multiple columns/tables

    I am trying to create a query that searches for a single string that is found in several columns in a few different tables. The following query takes about 6 seconds when adding the snUserComments.comment column to the search. There can be one or several comments associated with that particular sn_id in the snUserComments table. Is there a better way to search one or more columns for a string that will be faster (or higher performance)?

    Here is my query:



    SELECT DISTINCT


    snRequestData.sn_id,
    snRequestData.sn_title,
    snHistory.sn_state,
    snHistory.sn_nxt_actionee


    FROM snRequestData

    LEFT JOIN snCurrentState ON
    (snRequestData.sn_id = snCurrentState.sn_id)

    LEFT JOIN snHistory ON
    (snRequestData.sn_id = snHistory.sn_id AND snCurrentState.sn_id = snHistory.sn_id AND snCurrentState.sn_revdate = snHistory.sn_revdate)

    LEFT JOIN snAnalysisData ON
    (snRequestData.sn_id=snAnalysisData.sn_id)

    LEFT JOIN snWorkDone ON
    (snRequestData.sn_id=snWorkDone.sn_id)

    LEFT JOIN snUserComments ON (snRequestData.sn_id=snUserComments.sn_id)


    WHERE
    (snRequestData.sn_title LIKE '%key%' OR snRequestData.sn_desc LIKE '%key%' OR snAnalysisData.sn_analysis LIKE '%key%' OR snWorkDone.add_cmnt LIKE '%key%' OR snUserComments.comment LIKE '%key%')

  2. #2
    Join Date
    Mar 2015
    Posts
    4

    MySQL Performance Issue Help: String search in multiple columns/tables

    I am trying to create a query that searches for a single string that is found in several columns in a few different tables. The following query takes about 6 seconds when adding the snUserComments.comment column to the search. There can be one or several comments associated with that particular sn_id in the snUserComments table. Is there a better way to search one or more columns for a string that will be faster (or higher performance)?

    Here is my query:



    SELECT DISTINCT


    snRequestData.sn_id,
    snRequestData.sn_title,
    snHistory.sn_state,
    snHistory.sn_nxt_actionee


    FROM snRequestData

    LEFT JOIN snCurrentState ON
    (snRequestData.sn_id = snCurrentState.sn_id)

    LEFT JOIN snHistory ON
    (snRequestData.sn_id = snHistory.sn_id AND snCurrentState.sn_id = snHistory.sn_id AND snCurrentState.sn_revdate = snHistory.sn_revdate)

    LEFT JOIN snAnalysisData ON
    (snRequestData.sn_id=snAnalysisData.sn_id)

    LEFT JOIN snWorkDone ON
    (snRequestData.sn_id=snWorkDone.sn_id)

    LEFT JOIN snUserComments ON (snRequestData.sn_id=snUserComments.sn_id)


    WHERE
    (snRequestData.sn_title LIKE '%key%' OR snRequestData.sn_desc LIKE '%key%' OR snAnalysisData.sn_analysis LIKE '%key%' OR snWorkDone.add_cmnt LIKE '%key%' OR snUserComments.comment LIKE '%key%')

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    There is a logic error in your SQL that will slow you down. You have LEFT OUTER join coded on your snUserComments, snAnalysisData and snWorkDone yet, you have conditions for those tables in your WHERE clause. So, you have effectively turned those outer joins back into INNER joins. Best practice to keep all your predicates for a table in the ON clause of that table.
    Dave

  4. #4
    Join Date
    Mar 2015
    Posts
    4
    Dave,

    Thanks for your reply. Could you explain what you mean? Or provide an example for this on what I should be doing instead?

    Thanks!

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    WHERE
    (snRequestData.sn_title LIKE '%key%' OR snRequestData.sn_desc LIKE '%key%' OR snAnalysisData.sn_analysis LIKE '%key%' OR snWorkDone.add_cmnt LIKE '%key%' OR snUserComments.comment LIKE '%key%')
    You are now performing that portion of the where clause after you have done the LEFT OUTER to each of those tables.

    Wouldn't you really want your query like:

    Code:
    LEFT JOIN snAnalysisData ON 
     (snRequestData.sn_id=snAnalysisData.sn_id) 
    and snAnalysisData.sn_analysis LIKE '%key%'
    
     LEFT JOIN snWorkDone ON 
     (snRequestData.sn_id=snWorkDone.sn_id) 
    and snWorkDone.add_cmnt LIKE '%key%' 
    
     LEFT JOIN snUserComments ON (snRequestData.sn_id=snUserComments.sn_id) 
    and snUserComments.comment LIKE '%key%'
    
     WHERE 
     (snRequestData.sn_title LIKE '%key%' OR snRequestData.sn_desc LIKE '%key%' )
    Dave

  6. #6
    Join Date
    Mar 2015
    Posts
    4

    String Search - multiple tables & columns

    I edited my query based on your post and it's not giving me the correct results. I know that there should be 25 rows (or ID's) where the string 'key' is found when searching all those columns in the different tables. The table's snRequestData, snAnalysisData, and snWorkDone all have one row associated with a distinct sn_id. However, the table snUserComments has multiple rows with the same sn_id. See image links below to see how those tables are structured. Thanks so much for your help!! I really appreciate it. Have any ideas now that you can see how these tables are structured?

    Thank you


    snRequestData
    Click image for larger version. 

Name:	snRequestData.PNG 
Views:	2 
Size:	12.9 KB 
ID:	16254

    snAnalysisData
    Click image for larger version. 

Name:	snAnalysisData.PNG 
Views:	2 
Size:	8.1 KB 
ID:	16255

    snWorkDone
    Click image for larger version. 

Name:	snWorkDone.PNG 
Views:	3 
Size:	9.5 KB 
ID:	16257

    snUserComments
    Click image for larger version. 

Name:	snUserComments.PNG 
Views:	3 
Size:	13.9 KB 
ID:	16256

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •