Mysql Question posted in
The official MySQL documentation can be found here.

FullText MySQL search on keywords and description (two different tables – 1:N)

I’m seeking help regarding full-text searching in MySQL. I have two tables, Article and Keywords.

Article: id int, description (varchar(600))
Keyword: id int, value (varchar(50)), article_id int

An article can have multiple keywords. For example:
id:1, keyword1, article_id: 1
id:2, keyword2, article_id: 1
id:3, keyword1, article_id: 2
id:4, keyword3, article_id: 1

How can I correctly perform full-text searching on these data? Specifically, I need to search within article.description and keywords.value. It must be a combined full-text search. I need it to be as efficient and fast as possible, but I’m unsure since it’s a one-to-many relationship whether it will be slow and whether I have the data stored correctly.

I’d appreciate any advice.

I tried sql query with left join and subquery, but I am not sure if it is correct way how to do it.

Tags:

3

Answers


  1. 0

    I think you should do something like this

    SELECT a.id AS article_id, 
   a.description AS article_description, 
   k.value AS keyword_value
FROM Article a
JOIN Keyword k ON a.id = k.article_id
WHERE a.description LIKE '%search_term%'
   OR k.value LIKE '%search_term%'
ORDER BY a.id;
    Login or Signup to reply.
  2. 0

    You can’t make a single index include columns from more than one table, so you must define a separate index in each table.

    MySQL can’t do FULL OUTER JOIN (at least as of MySQL 8.3, cf. https://bugs.mysql.com/bug.php?id=18003), so you have to simulate it by doing a separate search query for each table.

    You may use UNION to combine the results.

    SELECT id FROM Articles WHERE MATCH(description) AGAINST ('...')
UNION 
SELECT article_id FROM Keyword WHERE MATCH(value) AGAINST('...');

    That gives you a list of article id’s which match your search criteria. You can put this in a derived-table subquery then join it to the Articles table if you need other columns from that table.

    SELECT Articles.*
FROM (
    SELECT id FROM Articles WHERE MATCH(description) AGAINST ('...')
    UNION 
    SELECT article_id FROM Keyword WHERE MATCH(value) AGAINST('...')
) AS srch
JOIN Articles USING (id);
    Login or Signup to reply.
  3. 0

    Not sure what is full text search, but I gues you want to know which keywords are cotained in some text. If that is the case then you could try it like below:

    --  S a m p l e    D a t a :
Create Table articles (ID Int, TXT Varchar(600));
Insert Into articles (ID, TXT) VALUES
(1, 'Some text with keyword1 and with keyword2'),
(2, 'Text with keyword1, with keyword3 and keyword1 again'),
(3, 'And text with no keywords at all');

Create Table keywords (ID Int, KEYWORD Varchar(16));
Insert Into keywords (ID, KEYWORD) VALUES
(1, 'keyword1'),
(2, 'keyword2'),
(3, 'keyword3');

    Option 1. One row per keyword per text

    Select a.ID, a.TXT, k.KEYWORD,
       FLOOR((Length(a.TXT) - Length(Replace(a.TXT, k.KEYWORD, ''))) / Length(k.KEYWORD)) "OCCURANCES"
From   articles a
Inner Join keywords k ON(InStr(a.TXT, k.KEYWORD) > 0)
Order By a.ID, k.ID;

    /*        R e s u l t :
ID  TXT                                                   KEYWORD   OCCURANCES
--- ----------------------------------------------------- --------- ----------
1   Some text with keyword1 and with keyword2             keyword1           1
1   Some text with keyword1 and with keyword2             keyword2           1
2   Text with keyword1, with keyword3 and keyword1 again  keyword1           2
2   Text with keyword1, with keyword3 and keyword1 again  keyword3           1  */

    Option 2. One row per text

    Select a.ID, a.TXT, 
      Group_Concat(Concat(Concat(k.KEYWORD, ' occurances is '), 
              FLOOR((Length(a.TXT) - Length(Replace(a.TXT, k.KEYWORD, ''))) / 
              Length(k.KEYWORD))) separator ', ') "KEYWORD_OCCURANCES"
From   articles a
Inner Join keywords k ON(InStr(a.TXT, k.KEYWORD) > 0)
Group By a.ID, a.TXT;

    /*        R e s u l t :
ID  TXT                                                   KEYWORD_OCCURANCES
--- ----------------------------------------------------- ------------------------------------------------
1   Some text with keyword1 and with keyword2             keyword1 occurances is 1, keyword2 occurances is 1
2   Text with keyword1, with keyword3 and keyword1 again  keyword1 occurances is 2, keyword3 occurances is 1    */

    Option 3. One row per text even when there are no keywords

    Select a.ID, a.TXT, 
      Group_Concat(Concat(Concat(k.KEYWORD, ' occurances is '), 
              FLOOR((Length(a.TXT) - Length(Replace(a.TXT, k.KEYWORD, ''))) / 
              Length(k.KEYWORD))) separator ', ') "KEYWORD_OCCURANCES"
From   articles a
Left Join keywords k ON(InStr(a.TXT, k.KEYWORD) > 0)
Group By a.ID, a.TXT;

    /*           R e s u l t : 
ID  TXT                                                   KEYWORD_OCCURANCES
--- ----------------------------------------------------- --------------------------------------------------
1   Some text with keyword1 and with keyword2             keyword2 occurances is 1, keyword1 occurances is 1
2   Text with keyword1, with keyword3 and keyword1 again  keyword3 occurances is 1, keyword1 occurances is 2
3   And text with no keywords at all                      null                                               */
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search