How to match and sort by similarity in MySQL?

Question

Currently, I am doing a search function. Lets say in my database, I have this data:

• Keyword1
• Keyword2
• Keyword3
• Keysomething
• Key

and the user entered: "Key" as the keyword to search. This is my current query:

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

Basically, I have 2 questions:

1. How do I sort by (order by) similarity. From above example, I wanted "Key" as my first result. My current result is: Keyword1, Keyword2, Keyword3, Keysomething and Key

2. My SQL query only search by the "data_string" column, what if I want to seach others column? Do 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 any better/faster query than Q2?

Answer 1

I am not sure if LIKE is the right way to do this. If you need to search inside your text for keywords and sort results by relevancy score, you should use MySQL Full-Text index and MySQL Full-text Search functions. Sorry if this leads you away from what you are actually trying to do but I do recommend having one look at it. Some quotes from MySQL reference manual:

1) How to create full text index on multiple columns of a table

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

2) Sample data

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) Sample query that searches multiple columns for keywords and displays result + the score:

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 |
+----+-------------------------------------+-----------------+