December 13th, 2013, 12:41 AM
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 ,'%')
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 .
December 13th, 2013, 09:53 AM
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?
December 15th, 2013, 12:12 AM
Originally Posted by craigtrombly
[Content] field data type is [TEXT]
[Keyword] field data type is [Varchar(20)]
December 15th, 2013, 10:01 AM
While I can appreciate the short response, with that, this article is the best response I can give you.
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.
Please let me know if this will help you resolve the response time.