--- title: Converting from MySQL to Elasticsearch description: Converting from MySQL to Elasticsearch --- [![e-Zest logo\_An accion lab company\_Colour](https://blog.e-zest.com/hubfs/images/Logos%20with%20Tagline/Corporate/SVG/e-Zest%20logo_An%20accion%20lab%20company_Colour.svg "e-Zest logo_An accion lab company_Colour")](https://www.e-zest.com/) - [WORK](https://www.e-zest.com/our-work) - [SERVICES](https://www.e-zest.com/our-services) - [Product Engineering](https://www.e-zest.com/digital-product-engineering-services) - [Enterprise Data Services](https://www.e-zest.com/enterprise-data-services) - [Digital Commerce](https://www.e-zest.com/digital-commerce-services) - [Digital Operations](https://www.e-zest.com/digital-operations) - [Digital Consulting](https://www.e-zest.com/digital-transformation-consulting-services) - [Digital Experience Design](https://www.e-zest.com/digital-experience-design) - [INSIGHTS](https://blog.e-zest.com/) - [CAREERS](https://www.e-zest.com/careers) - [ENTERPRISE DATA SERVICES](https://www.e-zest.com/enterprise-data-services) - [GENERATIVE AI](https://www.e-zest.com/generative-ai) - [Healthcare](https://healthcare.e-zest.com) - [Contact Us](https://www.e-zest.com/contact-us) - ![Elevating Product Engineering with AI and ML](https://blog.e-zest.com/hubfs/Elevating%20Product%20Engineering%20with%20AI%20and%20ML.jpg) [BLOGS](https://blog.e-zest.com) #### Elevating Product Engineering with AI and ML As technology evolves and customer choices broaden, there is an increasing ... [Read more](https://blog.e-zest.com/elevating-product-engineering-with-ai-and-ml) - ![e-Zest – An Accion Labs Company gets Recognition as a Contender in the ISG Provider Lens™ Study on Digital Engineering Services 2024](https://blog.e-zest.com/hubfs/e-Zest%20%E2%80%93%20An%20Accion%20Labs%20Company%20-%20Gets%20Recognition%20as%20a%20Contender%20in%20the%20ISG%20Provider%20LensTM%20Study%20on%20Digital%20Engineering%20Services%202024.jpg) [NEWS](https://blog.e-zest.com/news) #### e-Zest – An Accion Labs Company gets Recognition as a Contender in the ISG Provider Lens™ Study on Digital Engineering Services 2024 We are delighted to announce that e-Zest – An Accion Labs Company has been ... [Read more](https://blog.e-zest.com/news/e-zest-an-accion-labs-company-gets-recognition-as-a-contender-in-the-isg-provider-lens-study-on-digital-engineering-services-2024) - ![e-Zest Solutions supports Digital Pune Mission](https://blog.e-zest.com/hubfs/blog-files/Digital%20Literacy%20Bus%20e-Zest.png) [CSR](https://blog.e-zest.com/csr) #### e-Zest Solutions supports Digital Pune Mission **Pune, India:** e-Zest Solutions, a leading digital technology innovation ... [Read more](https://blog.e-zest.com/csr/e-zest-solutions-supports-digital-pune-mission) - [Work](https://www.e-zest.com/our-work) - [Services](https://www.e-zest.com/our-services) - [Insights](https://blog.e-zest.com) - [Careers](https://www.e-zest.com/careers) - [Enterprise Data](https://www.e-zest.com/enterprise-data-services) - [Generative AI](https://www.e-zest.com/generative-ai) - [Innovations](https://www.e-zest.com/innovations) - [Healthcare](https://healthcare.e-zest.com) - [About](https://www.e-zest.com/about-us) - [Contact](https://www.e-zest.com/contact-us) - [Blog](https://blog.e-zest.com/) - [News](https://blog.e-zest.com/news) - [CSR](https://blog.e-zest.com/csr) - [![Linkedin Icon](https://blog.e-zest.com/hubfs/icons/linkedin_icon_white.svg)](https://www.linkedin.com/company/e-zest-solutions/) - [![Twitter Icon](https://blog.e-zest.com/hubfs/twitter-logo-silhouette%20(5).svg)](https://twitter.com/ezest) - [![Facebook Icon](https://blog.e-zest.com/hubfs/facebook-app-logo%20(4).svg)](https://www.facebook.com/61259256638) - [![Instagram Icon](https://blog.e-zest.com/hubfs/instagram-logo%20(4).svg)](https://www.instagram.com/ezestsolutions/) - [![Youtube Icon](https://blog.e-zest.com/hubfs/icons/youtube_icon_white.svg)](https://www.youtube.com/user/ezestcorporate) [![Go to top icon](https://blog.e-zest.com/hubfs/AA%20Assets/top%20button.svg)](https://blog.e-zest.com/converting-from-mysql-to-elasticsearch/#top) Hit enter to search or ESC to close Insights e-Zest members share technology ideas to foster digital transformation. - [Blog](https://blog.e-zest.com) - [News](https://blog.e-zest.com/news) - [Podcasts](https://www.e-zest.com/podcasts) - [Healthcare](https://blog.e-zest.com/healthcare) - [CSR](https://blog.e-zest.com/csr) - [Whitepaper](https://blog.e-zest.com/whitepaper) - [Playbook](https://blog.e-zest.com/playbook) - [Media Kit](https://www.e-zest.com/media-kit) # Converting from MySQL to Elasticsearch [Mayur Jain](https://blog.e-zest.com/author/mayur-jain) Jun 25, 2014 [MySQL](https://blog.e-zest.com/topic/mysql) [NoSQL database](https://blog.e-zest.com/topic/nosql-database) [Elasticsearch](https://blog.e-zest.com/topic/elasticsearch) [cloud computing](https://blog.e-zest.com/topic/cloud-computing-2) [Technology](https://blog.e-zest.com/topic/technology) [RDBMS](https://blog.e-zest.com/topic/rdbms) - - - - While working on a project called [Lantanacloud](http://www.lantanacloud.com/) at e-Zest Solutions Ltd. India, the project was initially implemented using MySQL for database storage. Later on we decided to use ElasticSearch for two primary reasons: 1. Handle Big Data i.e. NoSQL 2. Handle Millions of data records on a daily basis Before I begin with the transition of MySQL to Elasticsearch you must know what is Elasticsearch, right ? Here's a brief description -- *Elasticsearch is* an end-to-end distributed, restful search and analytics platform. Some people have a misconception about Elasticsearch that it is just a search application, but Elasticsearch is actually an NoSQL database where you can store & retrieve data. As Wikipedia says : Elasticsearch is a search server based on [Lucene](http://en.wikipedia.org/wiki/Lucene). It provides a distributed, multitenant-capable full-text search engine with a [RESTful](http://en.wikipedia.org/wiki/Representational_state_transfer) web interface and schema-free [JSON](http://en.wikipedia.org/wiki/JSON) documents. Elasticsearch is developed in Java and is released as [open source](http://en.wikipedia.org/wiki/Open_source_software) under the terms of the [Apache License](http://en.wikipedia.org/wiki/Apache_License). For more details visit: [what is elasticsearch?](http://www.elasticsearch.org/overview/elasticsearch) Now moving on further, I would say, if your application is very big and complex then you have to identify those tables which you need to convert. I would suggest that you begin with those tables which consume lot of data and are queried more on a daily basis. Now you have to decide the database schema for Elasticsearch. Refer [this](http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/glossary.html) to understand the analogy between RDBMS & Elasticsearch. Next you need to understand the concepts of [sharding & replicas](http://stackoverflow.com/questions/15694724/shards-and-replicas-in-elasticsearch). To begin with Elasticsearch you first need to download and install it. Refer [installation](http://www.elasticsearch.org/guide/en/elasticsearch/client/php-api/current/_quickstart.html#_installation) for the steps. I prefer using the bulk API for indexing documents because the bulk API makes it possible to perform many index/delete operations in a single API call. This can greatly increase the indexing speed and reduces your number of API calls. Read data from database/your source with an iterator and buffer up them into an array. Then pass on this array to the body field of bulk API. The bulk API is mainly "bulk" in the sense that you can send a lot of stuff over at once, it doesn't allow Elasticsearch to do any special optimizations when inserting the data. **HOW I did it ?:** I created a class file where me & my colleague wrote many functions to perform various Elasticsearch operations: **Connect to Elasticsearch:** This function connects to the Elasticsearch and returns client handler. ``` /*Arguments: @param port : connection should be established at this 'port' @param host : connection should be established at this 'host' */ public function elasticsearch_connect( $host = 'localhost',$port = '9200' ) { $connect = array(); $hostPort = $host . ':' . $port; $connect['hosts'] = $hostPort; $this->;clientHdlr = new Elasticsearch\Client( $connect ); } ``` **Insert data into Elasticsearch:** This function inserts data into elasticsearch using BULK API. ``` /*Arguments: @param index : elasticsearch index in which data is to be inserted. @param type : elasticsearch type in which data is to be inserted. @param inputData : Array to be inserted. */ public function elasticsearch_insert( $index, $type, $inputData ) { $inputData['index'] = $index; $inputData['type'] = $type; $index_document = $this->clientHdlr->bulk( $inputData ); if($index_document) { return 1; } else { return 0; } } ``` **Check if index exits or not:** This function checks that index and type exists or not in the elasticsearch. ``` public function existsIndex( $index, $type ) { $checkArray = array(); $checkArray['index'] = $index; $checkArray['type'] = $type; $checkArray['id'] = 1; if( $this->clientHdlr->exists( $checkArray ) ) { return true; } else { return false; } } ``` **Search Operation:** This function is used to search a document in Elasticsearch. If we directly search for a particular document & if the index and type of the document are absent then we get an error and rest of the code execution stops. So we first check that the required index & type exists or not and if the document also exists then we return the result, but if the document is missing then we return a false. ``` /*Arguments: @param json : A search query in json format. */ public function searchIndex( $index, $type, $json ) { $searchArray = array(); $searchArray['index'] = $index; $searchArray['type'] = $type; $searchArray['body'] = $json; if( !( $this->existsIndex( $index, $type ) ) ) { return false; } else { $retArray = $this->clientHdlr->search( $searchArray ); if( $retArray['hits']['total'] == 0 ) { return false; } else { return $retArray ;} // Leaving with return array } } ``` **Get the Aggregate value:** This Function will return aggregated value of the intended field from elasticsearch. One problem of Elasticsearch is that it does not support auto-increment feature provided by MySQL. So before calling this function we first checked that the required index & type exists or not and if the document exists then we searched for the maximum value of that field using this function. By this method we used get the last value of the field that was indexed and then increment it before indexing further documents. ``` /*Arguments: @param fieldName : Name of the field for which aggregated value is to be fetched. @param aggType : Type of aggregation( e.g.: "max", "min", "mean" .... ). @return : Result of aggregation query. */ public function getAggIdValue( $index, $type, $fieldName, $aggType ) { $aggArray = array(); $aggArray['index'] = $index; $aggArray['type'] = $type; //multi search, i.e. search in multiple indices. $json = '{ "query" : { "match_all" : { } }, "facets" : { "'.$fieldName.'" : { "statistical" : { "field" : "'.$fieldName.'" } } } }'; $aggArray['body'] = $json; $retValue = $this->clientHdlr->search( $aggArray ); return $retValue['facets'][$fieldName][$aggType]; } ``` **Update a document:** When we update a document Elasticsearch basically overwrites the document. So to avoid re-writing the entire document again, I suggest the following steps: 1. Search the document to be modified and assign the result to a temporary array. 2. Assign the new values to the specific key-value pairs of the array that you to modify. 3. Again perform insert operation with this temporary array. **PLUGINS:** Elasticsearch has provided many plugins which prove to be very useful for analysis. To install them visit: [Plugins](http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/modules-plugins.html). For developers who like a good GUI to see how cluster looks like, I recommend them to install the **elasticsearch-head** plugin**.** Using this '**head'** plugin you can generate queries as well as test your own queries before you implement them in your code. I hope this post helps you a lot. Feel free to ask any doubts, drop comments, mail me. You can also share this post if you like. - - - ### Similar Blog [![Visualization of the time series data using Grafana](https://blog.e-zest.com/hubfs/Visualization%20of%20the%20time%20series%20data%20using%20Grafana-181220%20(1).png) Visualization of the time series data using Grafana The organizations that can take quick decisions have the potential to lead the industry. The decision-making ability of the ... By Anuja Vyavahare Nov 29, 2018](https://blog.e-zest.com/visualization-of-the-time-series-data-using-grafana) [![Aurora RDS vs MySQL RDS](https://blog.e-zest.com/hs-fs/file-3419272561-jpg/blog-files/rds.jpg) Aurora RDS vs MySQL RDS Amazon Aurora is a relational database engine that delivers the speed and reliability of commercial databases with the benefits ... By Manish Rangari Sep 22, 2015](https://blog.e-zest.com/aurora-rds-vs-mysql-rds/) [Pentaho configuration for MySQL **Steps to change Pentaho preconfigured hypersonic database to MySQL** By Shailesh Kulkarni Dec 26, 2012](https://blog.e-zest.com/pentaho-configuration-for-mysql/) [![Basic Apache Solr search analysis using PHP & log4j](https://blog.e-zest.com/hs-fs/file-3395200804-png/blog-files/solr-search-analysis.png) Basic Apache Solr search analysis using PHP & log4j If you are using Apache Solr as a search engine for your application then it is necessary to analyze Solr logs to improve your ... By Pravin Patil Oct 29, 2012](https://blog.e-zest.com/basic-apache-solr-search-analysis-using-php-log4j/) ## Helping Organizations Thrive in the Digital Age [Contact Us](https://www.e-zest.com/contact-us) - [About](https://www.e-zest.com/about-us) - [Services](https://www.e-zest.com/our-services) - [Work](https://www.e-zest.com/our-work) - [Insights](https://blog.e-zest.com/) - [Healthcare](https://healthcare.e-zest.com/) - [Accion Labs](https://www.accionlabs.com/) - - - - - - Dallas - Detroit - Hannover - Vienna - London - Pune © 2025 e-Zest Solutions - [Privacy Policy](https://www.e-zest.com/privacypolicy) - [Cookie Policy](https://www.e-zest.com/cookie-policy) Contact us Contact us e-Zest is a leading digital innovation partner for enterprises and technology companies that utilizes emerging technologies for creating engaging customers experiences. Being a customer-focused and technology-driven company, it always helps clients in crafting holistic business value for their software development efforts. It offers software development and consulting services for cloud computing, enterprise mobility, big data and analytics, user experience and digital commerce.