{"id":60680,"date":"2018-10-08T11:00:19","date_gmt":"2018-10-08T08:00:19","guid":{"rendered":"http:\/\/examples.javacodegeeks.com\/?p=60680"},"modified":"2018-10-05T09:51:51","modified_gmt":"2018-10-05T06:51:51","slug":"mysql-full-text-search-tutorial","status":"publish","type":"post","link":"https:\/\/examples.javacodegeeks.com\/java-development\/core-java\/sql\/mysql-full-text-search-tutorial\/","title":{"rendered":"MySQL Full-text Search Tutorial"},"content":{"rendered":"

1. Introduction<\/h2>\n

MySQL<\/a>\u00a0is one of the most used SQL databases in many of the world-enterprise applications due to its ability to manage transactions and the ACID-behaviour which is built into its core. One of the reasons MySQL is so popular is due to the easiness it provides for its usage and its related commands. In the open source web application framework LAMP<\/a> (which consist of Linux, Apache, MySQL and PHP),\u00a0MySQL server is a central & important component. The MySQL Database server is written using C and C++ which internally uses\u00a0a lexical analyser to parse and understand the SQL queries.<\/p>\n

With time, many full-text search databases have come up with fast indexes which are exclusively made for supporting scalable and efficient\u00a0full-text search functionality like Elasticsearch<\/a>, Apache Solr<\/a> and MongoDB<\/a> as well. That ease when you start typing something in a search box and results start to follow as you type, it is one of the implementations for full-text search.<\/p>\n

\n

Table Of Contents<\/h3>\n
\n
1. Introduction<\/a><\/dt>\n
2. MySQL Setup<\/a><\/dt>\n
3. Full-text Search with MySQL<\/a><\/dt>\n
4. Defining\u00a0Full-text indexes in MySQL<\/a><\/dt>\n
5. Introduction to\u00a0Full-text searches in MySQL<\/a><\/dt>\n
6. Boolean\u00a0Full-text searches in MySQL<\/a><\/dt>\n
7. Query Expansion with MySQL Full-Text Search<\/a><\/dt>\n
8. Performance of Full-text searches in MySQL<\/a><\/dt>\n
9. Comparison: MySQL vs Elasticsearch full-text search<\/a><\/dt>\n
10. Conclusion: MySQL Full-text Search<\/a><\/dt>\n<\/dl>\n<\/div>\n

2. MySQL Setup<\/h2>\n

For this lesson, we make a new server which we will be using as our MySQL installation. To proceed further, we need to set up MySQL Server and Client tools on that server. We can do this by executing the following command:<\/p>\n

Installing MySQL Server and Client<\/em><\/span><\/p>\n

sudo apt-get install mysql-server mysql-client\r\n<\/pre>\n
Once we run this command, the mentioned utilities will be installed on the server. While installing, we also set a MySQL root password:<\/p>\n
\n
\"MySQL<\/a>
Setting Root Password<\/figcaption><\/figure>\n<\/div>\n
Once the installation process is completed, we can confirm if the MySQL server is up and running with the following command:<\/p>\n
Check MySQL Service status<\/em><\/span><\/p>\n
sudo service mysql status\r\n<\/pre>\n
We will see an output like this:<\/p>\n
\n
\"MySQL<\/a>
Check MySQL Server Status<\/figcaption><\/figure>\n<\/div>\n
Now that we know that the MySQL server is up and running, we can connect to it using the username and password we supplied to it during the installation process. We can use the following command to do so:<\/p>\n
Logging into MySQL<\/em><\/span><\/p>\n
\n
\n
\n\n\n\n
1<\/code><\/td>\nmysql -u root -p<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n
When we press enter, MySQL Server will silently wait for us to provide the password. When we type it, the password will not be echoed back to the terminal due to security reasons. We will see the following prompt once we have logged into the MySQL command line:<\/p>\n
\n
\"MySQL<\/a>
MySQL Login<\/figcaption><\/figure>\n<\/div>\n
When we are inside the MySQL command prompt, we can use the given command to show the databases which are present in the system and to ensure that MySQL is running fine:<\/p>\n
Show All databases<\/em><\/span><\/p>\n
\n
\n
\n\n\n\n
1<\/code><\/td>\nshow databases;<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n
Here is what we get back with this command:<\/p>\n
\n
\"MySQL<\/a>
Check MySQL Databases<\/figcaption><\/figure>\n<\/div>\n
In the output, MySQL just displays the list of MySQL default databases that are used for administration purpose. As this is our fresh installation, only four databases are visible as of now which are made by MySQL itself. Now that we know that our MySQL set up is complete and running, we can start working with full-text search in MySQL but we must first understand properly what it is and how it works in MySQL.<\/p>\n
3. Full-text Search with MySQL<\/h2>\n
If you want to find textual data similar to given values, MySQL database supports the use of\u00a0LIKE<\/strong> operator and regular expressions as well. But these methods start to create performance bottlenecks when the amount of data grow in your database and text values grow in large number in themselves (like when we start to save complete paragraph or pages of documents in a database column). Let us point down the\u00a0important drawbacks<\/strong> for LIKE operator and regular expression usage:<\/p>\n