How to improve performance of e-commerce application using ElasticSearch
If you are familiar with Apache Solr or Sphinx - You will probably understand ElasticSearch as well.
Elasticsearch - agenda of the article1. What is Elastic Search for?
2. Performance problem - key: value database schema, lot's of joins, etc,
3. The solution of problem - ElasticSearch,
What is Elastic Search?What is Elastic Search?
Elasticsearch is a distributed, RESTful search and analytics engine capable of solving a growing number of use cases.
It's just a search database, using Zend Lucene engine for searching the most relevant results.
But ... this post goal is more about practice, so let's leave the theory.
Key Value DatabaseOur customer had a huge performance problem. Whole application structure, such as categories, products (e-commerce application), orders.. are stored in single Table (Key: Value). The code fragment (problematic) of the app is shown below:
As we can see, every object in the database (ex. Product) has his title and id. Other elements (such as description, photos) are joined from TaxonomyFields (Key: Value) table. The basic problem is the performance but... how about searching ? Yes, you can search by title... but what about searching for description? Big, slow query.
We have 30 products on a page. Every product has fields: description, description_en. 31 queries are generated. The reasons are as follows:
1. We fetch 30 TaxonomyItems (products) - 1 query,
2. For every single TaxonomyItem, we fetch the details from TaxonomyField table - 30 queries
1 + 30 = 31 queries.
When the real customers and suppliers registered on that platform, the performance problem occurs.
Some of the requests generated 3000 database queries! For a single user.
ElasticSearch is the solutionThe first solution, that we were thinking about was... redesign the database for standard entities, such as:
1. Product (id, title, description, photos)
2. Order (id, userId, List products, ...)
3. User (id, username, passwd, salt, ...)
But it would take too much time. We did not have so much time so....
The solution was... ElasticSearch.
We indexed the products with all the necessary attributes. It took 16 hours to write some piece of code that handles it.
Stage 1 - ElasticSearch structure for Product
Let's create the ProductIndex which will contain all the neccessary fields for a single Product item.
Every single Product will be generated from TaxonomyItem + (JOIN) TaxonomyFields MySQL Table.
We also included $categoryTree variable, that stores generated List categories, the products belongs to. It will be usefull for breadcrumbs for example.
Symfony Command Line for indexing all products from MySQL database.
As we can see, the buildProductIndex($product) method is doing all the job - it stores the data to ElasticSearch database.
The implementation is shown below:
Server resources usage has been reduced by 40%. Same like MySQL DB queries - reduced by 80-90%.
Homepage (Before 80 queries per req) => after ES implementation - 3 queries,
Product Search Page (Before 140 queries per req) => after ES implementation - 14 queries,
Category Page (Before 75 queries per req) => after ES implementation - 7 queries,
If you are interested more about ElasticSearch or performance solutions, feel free to contact us via e-Mail: firstname.lastname@example.org.
PHP and Java programmer with over 8 years of business experience.
During his career he was working for Polish, German, United Kindgdom and Nederlands customers. He created an e-commerce application for Trade Fair in Germans as well as take part of a team (as Solution architect) in a government project for a British customer.
Main topics of interests are a software architecture, third party integrations and custom API building.