Optimising MySQL database with read only tables

Question

I have a MySQL database with 15 tables. Three of them are used for website authentication and three other are tables that are frequently read and written to. Then other tables are set in stone and I'd never need to change them unless I make some significant changes to the web app.

Is there anything I can do to make my database more efficient and for performance, given that I don't need to write to these tables at runtime?

From Googling, I was able to see some settings for MyISAM, but would prefer to stick with InnoDB. Couldn't find any other info other than certain people having their entire database as read only. However, mine has some tables that I'd need to read/write.

EDIT:

Four tables are 4MB and below and one is 7.55MB. Also wouldn't I then need to adjust all my procedures to check whether the table in memory exists or not? Cause it may disappear when the instance is restarted or something.

• Akina: If these tables are tiny you can try to use copies of those tables (made while starting server) with ENGINE=Memory.
• Akina: Four tables are 4MB and below and one is 7.55MB. They are tiny. whether the table in memory exists or not? You will use memory copies unconditionally... copies table' names differs from hard source table' names, so you will no mix them. it may disappear when the instance is restarted or something. The memory table definition is static. Only its data is dynamic, and you will copy data to it from hardcopy in server startup shedule script...
• Akina: If I understand you right, I just need to duplicate those tables with the same name. No. You 1) rename original tables from old to new names; 2) create memory tables with old names; 3) copy table data from original (new names) tables to memory (old names) tables each time when server starts. None queries/SPs/another code needs in correction.
• Kondybas: @Akina MEMORY engine has significant limitation on column types at least. It would be better to migrate to the InnoDB and configure the memory pools big enough to fit all the data/indexes in the memory.
• Kondybas: @ShahidThaika MYISAM engine cached the indexes for tables (key_buffer_size option) separately while data cached as possible (read_buffer_size). InnoDB engine has data and indexes combined and cached into the memory reserved by innodb_buffer_pool_size option. If your tables are as small as described they are already are in-memory. Certainly if there are no other heavy databases on the server that share the InnoDB buffer pool. So if you are experienced some performance issues you have to inspect your queries/indexes, not the storage engine.

Answer 1

Short answer: You are making unnecessary work for yourself. Use InnoDB for everything. Forget about "readonly".

Long answer:

• MyISAM is dying.
• InnoDB is faster in some benchmarks than either MyISAM or MEMORY.
• InnoDB "caches" things in its "buffer_pool", which is in RAM. If you were using MEMORY to get things held in RAM, then "why bother".
• Because the buffer_pool is a "cache", there is no requirement that all the data and indexes be held in memory. Instead, only the "frequently" used blocks are kept in memory. Since it is at a block level, it is even better than a human trying to second guess what to pin in memory (via MEMORY engine) and what not to.
• Using space for MEMORY tables takes away from using RAM for the buffer_pool
• Both MyISAM and MEMORY lock the entire table (when needed); InnoDB locks only rows.
• True, MEMORY tables are not "persistent". InnoDB tables are persistent.

Bottom line...

• Use InnoDB for all of your tables.
• Set key_buffer_size to 20M only because some system tables use that engine.
• Set innodb_buffer_pool_size to 70% of RAM (or a smaller percentage if you have less than 4GB of RAM).

Answer 2

In MySQL You can Revoke and grant the specific privileges.

So we will REVOKE ALL privileges to specific database's table as you want and after it we will grant only SELECT privilege to it then it will become a read-only table:

REVOKE ALL PRIVILEGES ON db.table FROM user;
GRANT SELECT ON db.table TO user;