What is the best way to organize a database of a web service such as "home accounting"?

Question

PHP

I wanted to implement what is called out of interest and for experience, a web service for doing home accounting.
There are many such applications, the functionality for users usually looks like this in general terms:

• possibility to create multiple accounts/wallets
  
• the ability to create operations: income / expense / transfer (from one account to another) for each wallet
  
• the ability to edit completed transactions
  
• reports and graphs for selected periods
  

It looked at first glance not difficult, until I began to think in detail about the database and how the application would work.
Imagine that the service is used by many users over a long period of time. In this case, MySQL is used and a banal architecture was chosen with such tables: users, accounts, operations
Problem: the operations table will grow greatly over time, complex selections (over a period of time for several user wallets) will become, it seems to me, very costly and slow.
And in general, it seems to me not logical to store operations of different users in one table, because there will be no intersecting selections ...
So how is it better to design a database (or databases) in such a project?

Answer 1

prefixes in the names of tables with data of this type:
user1_operations
user2_operations

Answer 2

Greetings, I would do it the way you described, because the options for creating separate tables or a database are not suitable. Imagine then what will be the torment with migration.
In your case, you need to start with your architecture, in addition, you can always use the cache and do mysql replication, to start the master-slave.
Thus, queries will be executed in the same way to the database, but it will be cached, for example (for a day, and if a change occurs, reset the cache by this key).