Q:

How to properly search for data in a MySQL table

I have table in MySQL called movie like this
id    movie_name      movie_description
1   the godfather    The_godfather_description
2   the dark Knight  the_dark_knight_description
3   batman begins    batman_begins_description
4   Inception        Inception_description

When the query comes I am searching like this for each word in query
select movie_name,movie_description from movie where movie_name like(%is%) or movie_description like('%is%')
    select movie_name,movie_description from movie where movie_name like(%god%) or movie_description like('%god%')
   select movie_name,movie_description from movie where movie_name like(%father%) or movie_description like('%father%')

Can you tell me proper way to search database so that words like 'is' and 'the' get ignored.  I also want the most relevant articles on top.

A:

If I have got you correctly; you are searching for MySQL full Text search:
I want to explain you about Boolean Full Text Search; But I advise you to please go through Full Text Search using Query Expansion also.
Let's look at the example table:
mysql> select * from articles;
+----+-----------------------+------------------------------------------+
| id | title                 | body                                     |
+----+-----------------------+------------------------------------------+
|  1 | PostgreSQL Tutorial   | DBMS stands for DataBase ...             |
|  2 | How To Use MySQL Well | After you went through a ...             |
|  3 | Optimizing MySQL      | In this tutorial we will show ...        |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ...      |
|  5 | MySQL vs. YourSQL     | In the following database comparison ... |
|  6 | MySQL Security        | When configured properly, MySQL ...      |
+----+-----------------------+------------------------------------------+

mysql> SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('"database comparison"' IN BOOLEAN MODE);

+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+

Order matters, when the words are quoted:
mysql> SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('"comparison database"' IN BOOLEAN MODE);

Empty set (0.01 sec)

When we remove the quotes, it will search for rows, containing words "database" or "comparison":
mysql> SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('database comparison' IN BOOLEAN MODE);

+----+---------------------+------------------------------------------+
| id | title               | body                                     |
+----+---------------------+------------------------------------------+
|  1 | PostgreSQL Tutorial | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL   | In the following database comparison ... |
+----+---------------------+------------------------------------------+

Order doesn't matter now:
mysql> SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('comparison database' IN BOOLEAN MODE);

+----+---------------------+------------------------------------------+
| id | title               | body                                     |
+----+---------------------+------------------------------------------+
|  1 | PostgreSQL Tutorial | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL   | In the following database comparison ... |
+----+---------------------+------------------------------------------+

If we want to get rows, containing either word "PostgreSQL" or phrase "database comparison", we should use this request:
mysql> SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('PostgreSQL "database comparison"' IN BOOLEAN MODE);

+----+---------------------+------------------------------------------+
| id | title               | body                                     |
+----+---------------------+------------------------------------------+
|  1 | PostgreSQL Tutorial | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL   | In the following database comparison ... |
+----+---------------------+------------------------------------------+

fiddle
Make sure, that the words, you are searching for, are not in the list of stopwords, that are ignored.
(and obviously words like 'is','the' are stopwords and those are ignored)
I have explained here only about Boolean Full Text Search; But I advise you to please go through Full Text Search using Query Expansion also.

