How to Setup Elasticsearch With MySQL

by Ahmed Khan  October 4, 2016

Elasticsearch is an open-source full-text search engine. It allows you to store and search data in real time. The search time in Elasticsearch is considerably faster than SQL. You can also search for a phrase, and the engine will give you the results within seconds depending on how large the Elasticsearch database is.

how to setup elasticsearch with mysql

Suppose you are running a custom CMS, and its database has become too large, and as a result, its search time is slower than before. In this care, not only is Elasticsearch the best tool to increase the site search time, but it also helps the users search the complete text within your website quickly.

In this article, I will elaborate Elasticsearch in the context of a CMS. I will teach you how to connect your existing MySQL database with Elasticsearch and perform CRUD queries and perform a search from Elasticsearch database. I will be using the same Elasticsearch API “elasticsearch/elasticsearch”  which I used to connect PHP with Elasticsearch in my previous article.

Let’s get started.

Step 1: Create Custom Cluster and Node

Let’s create the custom cluster and node first. From a security perspective, it is a good practice to have a unique name for the cluster and node. Go to your Elasticsearch folder, open the config folder and then open elasticsearch.yml file in an editor. Name the cluster and node as:

Save the file. Now let us create a class which will perform CRUD and search operation in the cluster.

Step 2: Create CRUD Functions

I’ll create a separate class for handling CRUD operations and name this class searchelastic.

First, I loaded the elasticsearch/elasticsearch API library and then I created a private variable which will handle the connection of Elasticsearch inside the class. Next, I created a constructor so that whenever the class is called, a connection with Elasticsearch is created automatically. First, let’s create a function for Mapping data types to the fields in Elasticsearch:

Now we need a function that will gather all the data from MySQL database and save it in Elasticsearch database. Let’s create it using the following code:

In this function, I first called a MySQL database connection and performed a query which gathers all the articles from our database along with user names who published these articles.

Next, we save it in an array  $params[‘body’][].  Note that we are keeping the same ID as MySQL database which will help us in updating or deleting the data. Once entire data is fetched, I have mapped the data types by calling the Mapping() function. Next, I saved the array in Elasticsearch by calling $client->bulk($params);. bulk() is used to save bulk data. This function needs to be called only once when connecting MySQL database to Elasticsearch so that all the data that is saved in MySQL will be saved in Elasticsearch.

The functions I will create below will be recalled every time a new post is added, updated or deleted.

Let’s create a function which will add the newly created article in the Elasticsearch database now:

This function has two parameters; the ID of the newly created article, and a MySQL connection string. The function first fetches the data and then indexes it in Elasticsearch.

Now, let’s create a function for Update and Delete as well. The function will be similar to the one above except an Elasticsearch API function that will be changed.

Now that the CRUD functionality for Elasticsearch has been successfully created,  this class will be called whenever update or delete operations are carried out for any post. For example, when a new post is inserted into the MySQL database, I need to call InsertNode() in order to insert the post in Elasticsearch Database.

Now we’ll create a function which will search the user query in Elasticsearch data.

Step 3: Search the Elasticsearch Data

Searching the Elasticsearch database is simple. The process requires sending user query to Elasticsearch, which will then return the result for that query. Here’s the code to make a function for search in elasticsearch class:

Elasticsearch search() takes an array in which the index and query is submitted. Learn more about Elasticsearch search() here.

Conclusion

This article gave you a comprehensive step by step guide to making Elasticsearch as your custom search engine for MySQL powered PHP sites. I have created a CMS which uses Elasticsearch as the default site search, which is open source and can be found on my GitHub.

If you have any questions about the implementation and integration of Elasticsearch with MySQL in custom PHP websites, just leave a comment below and we’ll get back promptly!

Start Creating Web Apps on Managed Cloud Servers Now!

Easy Web App Deployment for Agencies, Developers and E-Commerce Industry

About Ahmed Khan

Ahmed was a PHP community expert at Cloudways - A Managed PHP Hosting Cloud Platform. He is a software engineer with extensive knowledge in PHP and SEO. He loves watching Game of Thrones is his free time. Follow Ahmed on Twitter to stay updated with his works. You can email him at ahmed.khan@cloudways.com

Stay Connected:

You Might Also Like...

  • johny

    no mapping and types casting? your ids and dates will be strings, and removing/updating will work with 50% chance)

    • Hi Johny,

      Thank you for pointing that out :). I have updated the article and also added Mapping in it.