Optimizing User Defined Custom Columns I am building a web based app that has the potential to have very high traffic and collect a lot of data. Most of this traffic will be utilizing the database. Database is MySQL InnoDB. I want to give users the ability to define their own data fields along with a predefined set of columns that are common. As an example I would have a table (restaurants) with the following columns:

record_id //INT PK auto_inc
  record_uuid //indexed stored as binary(16) 
  user_uuid //indexed stored as binary(16)  
  restaurant_uuid //indexed stored as binary(16)  
  date //indexed  
  price //indexed  
  party_count //indexed  
  rating //indexed  
  ...

User "A" would then add his own fields such as (chef_name,phone,table_count,rank)
While user "B" might add fields such as (contract_amount,license_fee,cuisine_type)

My thinking is to have a number of tables to hold a row of data for each one of the custom fields.  So I would have restaurants_extension_varchar, restaurants_extension_int and restaurants_extension_float.

Each of the three tables would look something like this:

extension_id //INT PK auto_increment 
extension_uuid //indexed stored as binary(16) used by app to update data value
record_id //indexed INT - used as a key to the main restaurants table 
field_identifier // TINYINT  
the_data //indexed  varchar/int/float - depending on which table this is 

I would then partition each of these tables into 6 partitions. The field identifiers would be held in a memory array for that user and would map the record to a label and page placement data for that custom field. These custom fields will be updated individually through Ajax and not as a group like when you click submit on a multi field form. I expect these tables to hold millions of rows. I have decided to use an auto increment ID as the primary key for all tables plus a UUID ID as the application ID that I will pass to and from the web app to prevent users from manipulating the primary key ID in an attempt to effect other rows. 

All of the custom fields need to be searchable. 

I have two questions. The first being about the structure. From a pure performance angle, is this a good solution or would I be better off with one row with an abundance of columns. 

The second question is regarding the use of an auto increment primary key ID along with an app generated uuid key which will be indexed. From what I understand if I use the uuid as the primary key it will be duplicated in each index that is created. I'm not a DBA so if there is any advice that can be offered I will be grateful. Thanks you!
A lot of people want to do what you are describing.  They get into big trouble when the dataset gets big.


  All of the custom fields need to be searchable. 


Only one solution:  MariaDB with "Dynamic Columns".  For discussion of why it is impractical otherwise, and discussion of lesser solutions:
http://mysql.rjweb.org/doc.php/eav


  an auto increment primary key ID along with an app generated uuid key which will be indexed


Once the table (or at least the index) is too big to be cached, UUID/GUIDs lead to lots of random disk I/O and destroy the cache.  Further discussion (and a workaround):  http://mysql.rjweb.org/doc.php/uuid