How to distribute tables?

Question

MySQL

Hello
I am studying the question of searching in MySQL tables
How to make an optimal database.
The site is a large bulletin board similar to Avito. The search will take place both in all ads and in categories and subcategories.
The base will have about 10 million entries active and 30 million inactive
Questions:
- Divide each category into a table or 1 table for all ads.
- Is it better to throw inactive ads in a separate table or just use the Status field?
- What is the best expression to use for searching? LIKE?

Answer 1

The database will have about 10 million active records and 30 million inactive records

If these are plans for the future - do not worry, when (and if) you grow up, you will have time to rewrite everything 10 times.
Depends on how different the ads are. It can be common, it can be separate, it can be common for intersecting fields + additional separate ones. In any case, 40,000,000 records is already the amount when you need to think about some kind of sharding of this business - and this is a much more interesting question.

Is it better to throw inactive ads in a separate table or just use the Status field?

It is worth storing in a separate table when the volume of active and archived files differs at least by an order of magnitude. In this case, this will not give much profit, but will add hemorrhoids.

- What is the best expression to use for searching? LIKE?

None. For search, especially on such volumes, it is necessary to use the tools intended for this. For example elasticsearch habrahabr.ru/company/2gis/blog/213765