Blog

Elasticsearch is the open source full text search engine that enables the users to store and search real time data. The search time in Elasticsearch will be reduced when compared to SQL. Based on the size of the database, Elasticsearch can provide results within seconds even when you enter a phrase.

Generally, when you run a custom CMS, the search time will be more if the database is large. Within your website, this tool helps to improve the search time and enables the users to find the complete text in your website faster when compared to SQL.

Here we shall discuss about connecting My SQL with Elasticsearch in the context of CMS. Here we can use the same API that we used to connect PHP to Elasticsearch search engine. Also, we can get the details of performing CRUD queries using Elasticsearch as well as performing a search using Elasticsearch database.

Step1

Creation of Custom Cluster and Node

You should create a unique custom cluster and node to proceed further to execute queries using the database. You can go to Elasticsearch folder, then move to config folder, and open elasticsearch.yml file in an editor. Then save the file and then perform CRUD and search operation in the saved cluster.

Step2

Creation of CRUD Function

You can create a separate class to handle CRUD operations and name this class as Searchelastic. Then you can load Elasticsearch API and then create a private variable. Then, create a constructor to create an Elasticsearch connection automatically whenever the Class is called. Then you can create function for mapping data types using the code to the fields in the database. Then you need a function to fetch data from MySQL database and save it in Elasticsearch database.

Then, you can connect the MySQL database and call all the articles and usernames saved in the particular database to be searched in Elasticsearch search engine. You can keep same ID as MySQL database to make any updation and deletions in the data using the My SQL server. Once the data is fetched, you can map it with data types using mapping function by calling it. This mapping function can be called only once when connecting My SQL database to the Elasticsearch database.

By this, all the data from the My SQL database will be saved into Elasticsearch Search database. Then you can create a calling function to recall whenever the data is updated, added or deleted.

This function will have two parameters. The ID of the new entry and the My SQL connected string. This recalling function fetches the data from the My SQL database and indexes it with Elasticsearch search engine.

Then, you need to create the function to update and delete the data in the Elasticsearch database. This function will be similar to the above one and the Elasticsearch API will be changed.

After the CRUD functionality will be created and updation and deletion functions are carried out using the Elasticsearch API. Whenever you need to add a new post, you can call Insert Node()in the Elasticsearch database.

Step 3

Then you need to create a function to call the user data into Elasticsearch search engine. The Search function is used to index the user data in the search engine and saves the user data in an array.
The process here is, we will send the user data into the search engine for processing and get the result to the query in return.
This method is used to save user data in Elasticsearch engine from My SQL database. Using Elasticsearch search engine, you can search for queries faster when compared to normal search engines.