How to optimize MySQL for processing a small database, i.e. <100mb?

Question

I am working on creating a database that is likely to remain less than 100 MB , has its own server, and will be read and modified through the Java EE web application on the intranet. I found a lot of optimization links for large databases, and I know that this is a much more important problem, but I have a lot of time, read / insert speed is the priority of the project, and I'm sure that I can somehow take advantage of such a small total db size. Of course, if MySQL is naturally not optimized for such a small test.

It is, of course, suitable for memory, but I need its data so that it is really saved on the disk, or at least it will be saved on the disk in the near future; I thought about some crazy alternatives, for example, sequentially loading the entire database into memory for the first time when it is needed (by user connection time, probably?), Somehow, and later it will be sent to disk.

But I thought it was better to ask about it here and see if someone had faced this situation before, and had a decent idea for profit from a small base size .

I think more in terms of access to the database, not the structure , although if someone has design tips for small databases and believes that they make the problem of access optimization completely irrelevant, saying that this is probably appropriate the answer also.

Thanks in advance.

Edit: the application is critical, and after I finish, it will be developed by guys who are mainly used for MySQL, so different DBMSs are not a great choice if they are not like MySQL.

Answer 1

Databases were handled in this way, so most of the infrastructure exists for you.

The burden is up to you:

• Create appropriate indexes based on data, volumn and dbms.

• Normalize data.

• Apply good checks - not nulls, uniques, etc.

• Use the explanation plan to find out how you can expedite your request — different for each situation.

• Use caching to improve performance.

• Make sure that the whole table has unique primary keys (obviously possible, but still required).

Answer 2

You can try looking at membase - I am told very good things regarding performance and perseverance. In essence, it is a “database” of memory that is stored on disk.