Using VIEW to reduce number of tables usedPeter Zaitsev
Many Open Source software solutions use database per user (or set of tables per user) which starts to cause problems if it is used on massive scale (blog hosting, forum hosting etc), resulting of hundreds of thousands if not millions of tables per server which can become really inefficient.
It is especially inefficient with Innodb tables both in terms of space (some tables would keep only couple of small rows, but require at least 16K page in Innodb), keeping all tables open in Innodb dictionary and number of other challenges in IO management and recovery. For MyISAM it works better but still overhead can get significant because table_cache can’t be made large enough and so a lot of table reopens needs to happen which requires table header modification, which is costly.
Of course if you can simply rewrite software to store multiple users per table it is best way to go, however quite typically this is way too much work and also requires constant patches as new software versions come out.
It is very tempting to use VIEWs to reduce number of tables dramatically – merging say 1000 of users to the same table as VIEWs are significantly less expensive and cheap to “open”.
How this could work ?
Say you have “post” tables which keeps blog posts and contains id, title, body columns (to keep it simple). There are one table per user so we have post123 table which keeps posts for user number 123 etc.
Converting it to the views we can have “post” table which has user_id,id, title,body columns and create post123 as view:
create view post123 as select id,title,body from post where user_id=123 with check option;
This would give us post123 containing only posts for users for user=123 which is exactly what we’re looking for. WITH CHECK OPTION is used to ensure we do not insert data in the view which will be invisible.
The VIEW approach works for SELECT, UPDATE, DELETE queries but not for INSERT:
mysql> insert into post123 values(1,"aaa","bbb");
ERROR 1369 (HY000): CHECK OPTION failed 'test.post123'
This happens because as we do not pass user_id value to underlying table MySQL tries to set it to default, which does not work as it has to be different for each of the views. You could think MySQL would look at the WHERE clause to figure out which value should be used but unfortunately it does not work this way.
What would be good to have is to specify different default fields for different views, though I would not expect it to happen as I do not think standard defines anything like it.
The other alternative would be to allow triggers on views, specially INSTEAD OF triggers so you could write triggers to perform insert inserts to base tables instead of views. BEFORE INSERT triggers would not help because the column user_id is not part of the view so would not be available even if MySQL would support triggers on VIEWs (which it does not)
But OK. That is all ideas – what can you do now ?
The obvious option is to go over the code and change inserts so they go in the base table – generally there should be only few places when insert happens so it should not be big problem.
Technically if you can’t make inserts to the base table but can have them going to the different table (typically would be very easy change) say “insertpost123” instead of “post123” you can create these insert tables as blackhole and define a triggers on them to update base tables, which will make rows available in the views. As the storage engine is BlackHole BEFORE triggers will act the same as INSTEAD OF triggers.
A bit ugly but can be helpful.