Best Mysql performance (one table or multiple tables)

Question

What has better performance (or how best to do) in a Mysql database, multiple tables, for example 'purchases_cliente_01, purchases_cliente_02' or a 'purchase_clients' table with the client ID ?

Example:

I have a system where I can register a customer and register purchases made by that customer. There is no product registration, only customer(name, Cpf, email...), and purchases(product, quantity, value...).

My question is whether I create a 'shopping' table for each customer example 'comprads_01' for the customer with ID 01, or if I create a 'shopping' table and put a 'ID_CLIENTE' column'.

Normally I would do the second way creating a 'shopping' table with the column 'ID_CLIENTE', but the question arose whether the queries will be faster if I do using a table for all purchases or a table of purchases per customer.

Ps.: You will not have many customers, but many purchases.

Answer 1

TL;DR: A table.

---

If you hadn’t put it in context, answer would be that there would be no difference, because if you forget the business rule to be applied, and understand only data storage, the two cases would solve the problem, and the performance would remain. Based on the first case, and disregarding the fact that there could be thousands of purchase tables, and assuming would have a very ingenious way to harvest the data, it would work. The problem is that it is not usual to do this Augusto. We have some rules that help solve this problem and ensure consistent storage. I suggest reading on Data Normalization:

Data normalisation Database normalization is the process of transformations in the structure of a database that aims to eliminating redundancies and eliminating insertion anomalies, update and exclusion. _{Source: What is bank normalization of dice?}