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!

1 Answer 1


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

  • Rick, thanks for your quick answer and for the info you have provided. I looked at MariaDB and it looks like a perfect solution for my needs!
    – Charles09
    Commented Oct 23, 2012 at 23:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.