Keyword researchs in innoDB MySQL

Question

Problem: I have a table containing "keywords" associated to a "label". I have to find the label associated to an input string thanks to a query.

Example:

DB (table):
keywords| label     | weight
PLOP    | ploplabel | 12
PLOP    | ploplbl   | 8
TOTO    | totolabel | 4
...     | ...       | ...

Input string : "PLOP 123"

Should return: "ploplabel"

The first instinctive query in my mind, for partial keywords reasearch, was:

SELECT label FROM table WHERE keywords LIKE "%inputstring%" ORDER BY weight DESC

But as you may have seen, it is the opposite I would need, something like:

SELECT label FROM table WHERE %keywords% LIKE "inputstring" ORDER BY weight DESC

Is it something we can do in MySQL (innoDB === no fulltext)?

Mmh... sorry if I wasn't clear. Of course it's wrong, what I meant is that I was looking for a query having this behavior. Thx — Mark, Mar 15 '11 at 11:57

score 0 · Answer 1 · edited May 23 '17 at 12:19

0

Build your system using innodb and create a myisam fulltext table to index back into your innodb data. That way you get all the advantages of the innodb engine: clustered primary key indexes, row level locking and transactions supplemented by the fulltext capabilities of one or more myisam tables.

Any way to achieve fulltext-like search on InnoDB

edited May 23 '17 at 12:19

Community

1
1

answered Mar 15 '11 at 10:23

Jon Black

16,223
5
43
42

So I do have to create a MyIsam table and fill it with batches... I really really hoped there was another way. Thanks f00 ! – Mark Mar 15 '11 at 11:53

Keyword researchs in innoDB MySQL

1 Answers1