How to ensure the fast operation of a site with a large database?

Question

Database

For the store, I need to make an inventory management system, I will look for a developer, now I am compiling a TOR. I thought that I may not fully understand how to describe the stability of the system in the TK. And the developer still does not know which one will fall.
The fact is that the store is specific, each product has a unique characteristic, and now there are under 300,000 of them, by the end of the year I think it will be under a million, and the number will continue to grow. These characteristics have about 10 similar data, such as the product name, date added, status, etc., but the volume is still very large. There is no way to change this. There will be other tables, but they are relatively small.
I thought that the solution can be done on laravel + mysql. It’s good for me, because I myself superficially understand how it works. But will this framework be able to consistently process such volumes of data? Where can there be pitfalls here?
It is very important that the system works quickly and very stable.

Answer 1

I think in this case it is important to choose a competent contractor. I have seen a lot of sites in my lifetime, but 95% of them were written crookedly, you can count smart developers on the fingers, and they usually cost more than "$100 per site" (a favorite phrase of freelance customers :)
Just yesterday, a major American service was optimized. Task - the page opens for more than 15 seconds. A simple analysis of the slologs and corny correct indexing allowed us to reduce the loading time to 0.6 seconds, and these are elementary things that any developer should know.
Simply, when you are looking for a customer, demand his portfolio with high-load projects. You can ask, for example: "hmm, I see a lot of products on your site with a large number of parameters for each. Tell us how you built the sql query and what was the approximate time it took to query the database. How did you achieve such a good time?" . According to his answer, even if you yourself do not understand half of the words, it will be clear whether he is in the subject or not.

Answer 2

Leave the choice of technologies to the contractor, because you have about the knowledge of a plinth on this issue. Write Quality Attributes for acceptance. If all the necessary load tests are performed, then even if it works on files)
PS about files, of course, it's too much, but who's stopping you from writing restrictions on the same technologies in the requirements in the TOR. As an example, a relational free database with LTS until at least 2040. And what will be - after that it is not so important. Same story with programming language.

Answer 3

The framework has nothing to do with it at all. This is a matter of proper organization of the database structure, settings, and indexes.
1 million is not a lot. I would still take PostgreSQL, and store the changing characteristics in a jsonb field with a GIN index on it.
The rest depends on specific tasks, but on one project, searching for 600k of such indexed JSONs took ~ 10ms.
So there will be no problems with a million. But with a crooked structure, and requests ... I saw a store that, with 70k products, generated pagination for 4 minutes. On shared hosting, it’s true, on a simple server it was already 8 seconds, which, incidentally, is also out of the question.

Answer 4

If you don’t go too deep, then do what is done. The base will withstand without problems and lam records. If there is a problem - add indexes. If there is a lot of extra data, write a cleaner for old records. In general, all projects that are developing, the base is always growing - this is normal.