CODEWITHSUNDEEP
mysql
kurikaktus
kurikaktus

Reputation: 15

SQL using "in" for looking up substrings

So, we know this one works when I want to select all ID's that are present in the inner sql statement

Select * 
FROM TableA 
WHERE Column1 IN (SELECT column2 FROM tableB WHERE = condition)

What kind of syntax do I need to do if Column1 is a long string and I need to check if a certain substring exists.

Ex Column1 = "text text text text 12345" where 12345 is an ID that is present in the list of ID's given by the inner sql statement

Basically I'm trying to detect if an ID is present in one of strings from another table based on my list of ID's from another table.

Should I do this in SQL or let a serverside code do it?

Upvotes: 0

Views: 34

Answers (2)

Shadow
Shadow

Reputation: 34230

Instead of using fieldname like '%needle%' search, which is extremely slow because it cannot utilise indexes, create a fulltext index on the given column and use fulltext search to find the matching substring.

Below code excerpt is quoted from the MySQL documentation:

CREATE TABLE articles (
      id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
      title VARCHAR(200),
      body TEXT,
      FULLTEXT (title,body)
    ) ENGINE=InnoDB;

SELECT * FROM articles
    WHERE MATCH (title,body)
    AGAINST ('database' IN NATURAL LANGUAGE MODE);

The catch with syntax is that the list of words being looked for ('database' in the above code example) must be a string literal, it cannot be a subquery. You need to assemble the list of keywords in the application that calls the sql statement.

Upvotes: 1

arkascha
arkascha

Reputation: 42885

This is usually done using the LIKE operator: 

SELECT ... FROM ... WHERE Column1 LIKE "%12345%";

However this is extremely slow, since it is based on substring matching. To improve performance you have to create a search index table storing single words. Such index typically is maintained by trigger definitions: whenever an entry is changed the trigger also changes the set of words extracted into the search index table. Searching in such an index table is obviously fast and can be combined with the original table by means of a JOIN based on the n:1 relationship between words in the index to the original entries in your table.

Upvotes: 3

Related Questions