MyISAM/table per user or InnoDB/shared table set

MySQL & MariaDB Other MySQL® Questions
1

Hello,

in a web application I am working on each user has an own table set, so total amount of tables is hundreds of thousands. All tables are InnoDB.

And, as you have probably already guessed, we have some performance problems.

Reviewing all possible ways of improving performance, would changing table types to MyISAM, while keeping an own table set per user, increase performance?

And taking in account the fact that MyISAM does table-level locks, as opposed to InnoDB’s row-level locks, is that a huge risk to merge all tables into a single MyISAM table set?

Thanks

2

Changing to MyISAM while still keeping one table/per user is not a good idea, you will still have a lot of performance problems.

One table for all users is the way forward.

MyISAM table locking is an issue if your application is performing a lot of UPDATE or DELETE’s.
If you have mostly SELECT’s then table locking is not an issue for performance.

My recommendation is that you consolidate your tables into one table for all users but still keep using InnoDB.