How to match and sort by similarity in MySQL?

Question

I am currently performing a search function. Suppose my database has the following data:

• Keyword 1
• word 2
• KEYWORD3
• Keysomething
• Key

and the user entered: "Key" as a search keyword. This is my current request:

SELECT * FROM data WHERE (
  data_string LIKE '$key%' OR 
  data_string LIKE '%$key%' OR
  data_string LIKE '%$key'
)

Basically, I have 2 questions:

• How to sort (in order) similarity. From the above example, I wanted Key to be my first result. My current result: keyword1, keyword2, keyword3, keyword and key

• My SQL query only searches the column "data_string", what if I want to see other columns? I need to do something like this:

SELECT * FROM data WHERE (
  data_string LIKE '$key%' OR
  data_string LIKE '%$key%' OR
  data_string LIKE '%$key'
) OR (
  data_other LIKE '$key%' OR
  data_other LIKE '%$key%' OR
  data_other LIKE '%$key'
) -- ...

Is there a better / faster request than Q2?

Answer 1

, LIKE - . , MySQL Full-Text index MySQL. , , , . MySQL:

1)

mysql> CREATE TABLE articles (
    ->   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->   title VARCHAR(200),
    ->   body TEXT,
    ->   FULLTEXT (title,body)
    -> );

2)

mysql> INSERT INTO articles (title,body) VALUES
    -> ('MySQL Tutorial','DBMS stands for DataBase ...'),
    -> ('How To Use MySQL Well','After you went through a ...'),
    -> ('Optimizing MySQL','In this tutorial we will show ...'),
    -> ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    -> ('MySQL vs. YourSQL','In the following database comparison ...'),
    -> ('MySQL Security','When configured properly, MySQL ...');

3) , + :

mysql> SELECT id, body, MATCH (title,body) AGAINST
    -> ('Security implications of running MySQL as root') AS score
    -> FROM articles WHERE MATCH (title,body) AGAINST
    -> ('Security implications of running MySQL as root');
+----+-------------------------------------+-----------------+
| id | body                                | score           |
+----+-------------------------------------+-----------------+
|  4 | 1. Never run mysqld as root. 2. ... | 1.5219271183014 |
|  6 | When configured properly, MySQL ... | 1.3114095926285 |
+----+-------------------------------------+-----------------+