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.

📣 Try the fastest hosting platform with pay-as-you-go pricing & 24/7 expert support! MIGRATE NOW →

How to Setup Elasticsearch With MySQL

Updated on June 15, 2021

5 Min Read

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

Host PHP Websites with Ease [Starts at $10 Credit]

  • Free Staging
  • Free backup
  • PHP 8.0
  • Unlimited Websites

TRY NOW

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:

Cluster.name = blog
Node.name = articles

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.

<?php

require 'vendor/autoload.php';

class SearchElastic

{

   private $elasticclient = null;

    public function __construct()

   {

       $this->elasticclient = Elasticsearch\ClientBuilder::create()->build();

   }

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:

 public function Mapping(){
                $params = [
                    'index' => 'articles',
                    'body' => [
                        'mappings' => [
                            'article' => [
                                'properties' => [
                                    'id' => [
                                        'type' => 'integer'
                                     
                                    ],
                                    'article_name' => [
                                        'type' => 'string'
                                     
                                    ],
                                    'article_content' => [
                                        'type' => 'string'
                                     
                                    ],
                                    'article_url' => [
                                        'type' => 'string'
                                     
                                    ],
                                    'category_name' => [
                                        'type' => 'string'
                                     
                                    ],
                                    'username' => [
                                        'type' => 'string'
                                     
                                    ],
                                    'date' => [
                                        'type' => 'date',
                                        'format' => 'dd-MM-yyyy'
                                    ],
                                    'article_img' => [
                                        'type' => 'string'
                                     
                                    ],
                                ]
                            ]
                        ]
                    ]
                ];
       $this->elasticclient->indices()->create($params);
       
    }

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:

public function InsertData($conn)
	{
	$con = $conn;
	$client = $this->elasticclient;
	$sql = "SELECT articles.article_id,articles.article_name,articles.article_content,articles.img,articles.url,categories.category_name,CONCAT(users.u_fname,' ',users.u_lname) AS username,DATE_FORMAT(articles.date,'%d-%m-%Y') AS dates FROM article INNER JOIN users ON users.user_id = article.user_Id INNER JOIN articles ON articles.article_id = article.article_id INNER JOIN categories ON categories.category_id = articles.category_id ";
	$result = $con->query($stmt);
	$params = null;
	while ($row = $result->fetch_assoc())
		{
		$params['body'][] = array(
			'index' => array(
				'_index' => 'articles',
				'_type' => 'article',
				'_id' => $row['article_id'],
			) ,
		);
		$params['body'][] = ['article_name' => $row['article_name'], 'article_content' => $row['article_content'], 'article_url' => $row['url'], 'category_name' => $row['category_name'], 'username' => $row['username'], 'date' => $row['dates'], 'article_img' => $row['img'], ];
		}

	$this->Mapping();
	$responses = $client->bulk($params);
	return true;
	}

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:

  public function InsertNode($articleid, $con)

   {

       $conn = $con;

       $client = $this->elasticclient;

       $stmt = "SELECT articles.article_id,articles.article_name,articles.article_content,articles.img,articles.url,categories.category_name,CONCAT(users.u_fname,' ',users.u_lname) AS username,DATE_FORMAT(articles.date,'%d-%m-%Y') AS dates FROM article INNER JOIN users ON users.user_id = article.user_Id INNER JOIN articles ON articles.article_id = article.article_id INNER JOIN categories ON categories.category_id = articles.category_id WHERE articles.article_id = $articleid";

       $result = $con->query($stmt);

       $params = null;

       while ($row = $result->fetch_assoc()) {

           $params = ['index' => 'articles', 'type' => 'article', 'id' => $row['article_id'], 'body' => ['article_name' => $row['article_name'], 'article_content' => $row['article_content'], 'article_url' => $row['url'], 'category_name' => $row['category_name'], 'username' => $row['username'], 'date' => $row['dates'], 'article_img' => $row['img'], ]];

       }

       $responses = $client->index($params);

       return true;

   }

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.

   public function UpdateNode($articleid, $con)

   {

       $conn = $con;

       $client = $this->elasticclient;

       $stmt = "SELECT articles.article_id,articles.article_name,articles.article_content,articles.img,articles.url,categories.category_name,CONCAT(users.u_fname,' ',users.u_lname) AS username,DATE_FORMAT(articles.date,'%d-%m-%Y') AS dates FROM article INNER JOIN users ON users.user_id = article.user_Id INNER JOIN articles ON articles.article_id = article.article_id INNER JOIN categories ON categories.category_id = articles.category_id WHERE articles.article_id = $articleid";

       $result = $con->query($stmt);

       $params = null;

       while ($row = $result->fetch_assoc()) {

           $params = ['index' => 'articles', 'type' => 'article', 'id' => $row['article_id'], 'body' => ['article_name' => $row['article_name'], 'article_content' => $row['article_content'], 'article_url' => $row['article_id'], 'category_name' => $row['category_name'], 'username' => $row['username'], 'date' => $row['dates'], 'article_img' => $row['img'], ]];

       }

       $responses = $client->update($params);

       return true;

   }

   public function DeleteNode($id)

   {

       $client = $this->elasticclient;

       $params = ['index' => 'articles', 'type' => 'article', 'id' => $id, ];

       $responses = $client->delete($params);

       return true;

   }

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:

   public function Search($query)

   {

       $client = $this->elasticclient;

       $result = array();

       $i = 0;

       $params = ['index' => 'articles', 'type' => 'article', 'body' => ['query' => ['match' => ['article_content' => $query], ], ], ];

       $query = $client->search($params);

       $hits = sizeof($query['hits']['hits']);

       $hit = $query['hits']['hits'];

       $result['searchfound'] = $hits;

       while ($i < $hits) {

           $result['result'][$i] = $query['hits']['hits'][$i]['_source'];

           $i++;

       }

       return $result;

   }

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

Supercharged Managed PHP Hosting – Improve Your PHP App Speed by 300%

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!

Share your opinion in the comment section. COMMENT NOW

Share This Article

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.

×

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

Thankyou for Subscribing Us!

×

Webinar: How to Get 100% Scores on Core Web Vitals

Join Joe Williams & Aleksandar Savkovic on 29th of March, 2021.

Do you like what you read?

Get the Latest Updates

Share Your Feedback

Please insert Content

Thank you for your feedback!

Do you like what you read?

Get the Latest Updates

Share Your Feedback

Please insert Content

Thank you for your feedback!

Want to Experience the Cloudways Platform in Its Full Glory?

Take a FREE guided tour of Cloudways and see for yourself how easily you can manage your server & apps on the leading cloud-hosting platform.

Start my tour

CYBER WEEK SAVINGS

  • 0

    Days

  • 0

    Hours

  • 0

    Mints

  • 0

    Sec

GET OFFER

For 4 Months &
40 Free Migrations

For 4 Months &
40 Free Migrations

Upgrade Now