+ Reply to Thread
Results 1 to 4 of 4
  1. #1
    Junior Member
    Join Date
    Dec 2013
    Posts
    3

    String Comparsion

    Hi ,

    I am new to ICE . I am doing string comparsion using below query , can anyone help me in optimizing the below query .

    SELECT t1.ContentID,t2.Sub_CategoryID,t2.Keywords,'Not' Type
    FROM Data_Records t1
    INNER JOIN keywords t2 ON t1.Content LIKE CONCAT('%', t2.Keyword ,'%')
    where
    t2.type='<filter>'

    both tables created in brighthouse engine. Content filed is of text datatype. The above query is taking around 10-15 min .

    I have around 500k records in Data_Records table and 2k records in keywords. I am searching whetheer keyword is present in content field or not .

  2. #2
    Can you please give an explanation of the 2 tables and an idea of the type of data or strings that are going to be in the relevant columns for searching? I need to get an idea on the column lengths and data distribution in the column to help determine what would be he best approach. Is there any way possible that you could post the bh.err file from the last time you ran the query?

  3. #3
    Junior Member
    Join Date
    Dec 2013
    Posts
    3
    Quote Originally Posted by craigtrombly View Post
    Can you please give an explanation of the 2 tables and an idea of the type of data or strings that are going to be in the relevant columns for searching? I need to get an idea on the column lengths and data distribution in the column to help determine what would be he best approach. Is there any way possible that you could post the bh.err file from the last time you ran the query?

    [Content] field data type is [TEXT]
    [Keyword] field data type is [Varchar(20)]

  4. #4
    While I can appreciate the short response, with that, this article is the best response I can give you.

    http://www.infobright.org/images/upl...Infobright.pdf

    Without knowing the kind of distribution of the data, I might have been able to suggest using a LOOKUP column which would give you even faster response.

    https://www.infobright.org/index.php...o-use-lookups/

    Please let me know if this will help you resolve the response time.

 

 

Posting Permissions

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