The website and online store "Eldorado" is about 40 thousand purchases every day. There is probably no need to explain what this means for the company's business.
Historically, the store runs on the Bitrix engine with a huge amount of custom code and add-ons. The storage is a MySQL cluster with four master servers.
A significant number of companies have monolithic applications, and many have to work with them. There are plenty of ways to deal with the monolith, but, unfortunately, few people write about successful ones. I hope that the story of how we prop up our monolith (until we saw it) will be of interest to you.
We are well aware that massive architecture can bring a lot of problems. But it is easy to destroy it, by a simple strong-willed decision, it is impossible: sales are going, the site should work, changes for users should not be radical. Therefore, the transition from a monolith to a set of microservices takes time, which we need to hold out: to ensure the system is operational and its resistance to stress.
I think that each of you tried to find the own way to solve tasks in the learning process of a relational database management system, and not knowing that out there are various helpful features, which could speed up the queries at times and reduce the code size. In this article I want to share with you my experience, namely how to work with MySQL comfortably, often allowing the programmer to do the things that other databases would not be able to do. This material would be useful rather for those who just decided to delve into the wonderful world of queries, but maybe the experienced programmers will find something interesting for themselves here.
We all remember the classical explanation about the indexes in the database and how they make the task easier to find the right lines. I'm sure most of you visualizes something like this:
It becomes clear right away that it takes much less effort to find two or three right lines throughout the data. It is brilliant, easy, and clear.
Personally, I always thought that there is no room for any improvement regarding this method until I got familiar with the clustered indexes. It turned out that the non-clustered indexes are not that perfect as I though.
So here are some questions: What is exactly a clustered index? Why is it better than the non-clustered one? What’s going on with it in MySQL?