Chat with us, powered by LiveChat

This website uses cookies

Our website, platform and/or any sub domains use cookies to understand how you use our services, and to improve both your experience and our marketing relevance.

Launch PHP Websites and Applications in seconds through our 1-Click App Install Launch Now!

How to Setup Elasticsearch With MySQL

Updated on October 4, 2016

5 Min Read
Reading Time: 5 minutes

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 on a PHP MySQL hosting, 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 in PHP and MySQL 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.

You might also like: How To Connect MySQL Database With PHP Websites

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.

You Might Also Like: MySQL Performance Tuning for Query Optimization

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.


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!

Share your opinion in the comment section. COMMENT NOW

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

Launch PHP websites without the worry of Server Management.

Pre-Installed Optimized Stack with Git, Composer & SSH

Get Our Newsletter
Be the first to get the latest updates and tutorials.