Merging multiple tables

MySQL & MariaDB Other MySQL® Questions
1

After some research on the topic, I am looking towards merging multiple table sets (one per user) to a single set of tables with an extra field in each table for identifying the user a particular record belongs to.

I’d prefer to make the abstraction layer on the database side to keep the changes in the application minimal. For that purpose, I am going to use triggers and create a set of views for each user.

My questions are the following:

  1. I don’t know how MySQL handles views, so is there an extra overhead in database having a lot (millions) of views?

  2. As I understand, all indexes should be prepended by user_id field, so instead of:

create table username_posts ( id integer not null auto_increment , title varchar(32) , contents text , primary key (id) , key (title) );

I would have to use:

create table global_posts ( id integer not null auto_increment , title varchar(32) , contents text , user_id integer not null , primary key (user_id, id) , foreign key (user_id) references users(id) , key (user_id, title) );

But I get “Error in table definition”, as I am using InnoDB and it requires auto_increment column to be first in the list. Is there any way around it?

  1. Are there any other issues I should take into account when doing the migration?