What is the best way to design a MySQL database?

Question

MySQL

Hello friends. I faced the problem of optimizing one of my projects, so I decided to ask you for advice. First, I will briefly describe the situation:
There is a service that allows people to apply. Each application is considered (approved) automatically, the approval operation requires heavy selections from the database (with isolated transactions). Now the application approval operation occurs immediately upon pressing the submit button, making a heavy selection each time someone submits an application.
The application operation is available through the API, so the server can be very busy when the software is running. I will immediately note that if the application is approved, then it is placed in the table of active applications, and the software is given its ID in this table, through which further access to the application takes place. If the application is not approved, the reason for which this happened is given.
After a little thought, I found the ideal (as it seemed to me) solution - to make a pool of requests (a separate MEMORY table) and process it every few seconds. This will reduce the number of heavy fetches from the database.
To check, the solution turned out to be not so perfect. A number of problems arise: after submitting an application, it will be necessary to return an identifier by which the client can find out the status of the application. The obvious solution is the order ID in the pool (MEMORY table). But:
- how to save this ID for the table of successful applications with this approach? If you assign a temporary ID to successful orders, then there will be just huge gaps in the table of successful orders, and this is clearly not good.
- how to notify the client about the reasons for refusal to approve the application, if after its consideration the record from the temporary table is deleted? Create a table for unapproved applications (or store them in an approved table)? This is somehow not beautiful, given that there are hundreds of times more unapproved.
I would be glad to hear any of your thoughts on the problem. Thank you.

Answer 1

Is it possible to transfer this pool to a regular table, but to work with it as with a pool? An application has arrived - it is simply entered into the application table, after a while the pool processor starts working with the last unprocessed applications, entering the result of their distribution into the same table. The results and id do not disappear anywhere, so you can use them for notifications at any time.

Answer 2

How many records are in the database, how many new ones are received per day? A "heavy" sample is how much of what and where does it pull from?

Answer 3

MEMORY storage in MySQL does not work properly and never has. It does not give any advantages over a properly configured InnoDB: firstly, it is not faster, and secondly, only hash indexes are possible in it and, as a result, range scans are impossible. Still there, it seems, there are no line-by-line locks - but here it is necessary to clarify, I don’t remember exactly.
What exactly are you trying to optimize? What exactly is the request? Have you tried tweaking the InnoDB settings? What settings and how did you set them up?