2

I am aware that this has been discussed before, but I've read conflicting information about this particular topic, so I'm hoping for a bit more decisive opinions from you.

I have multiple tables, which stand in a 1:1 relationship to a main table linked by a foreign key.

The largest of these tables, the parameter table, has about 1000 columns by now, each of the columns holding a single numerical parameter.

The table in question has a size of about 16 MB with 7000 rows right now.

After creating a normalized structure (ID, Key, Value) and inserting all the data into this new table, it turned into 3 million rows (after filtering out the columns that hadn't been used in the table with the many columns) and a size of 107 MB.

Now I'm wondering, if the effort is really worth the gain. Sure, it's a lot more scalable (I've had to add columns manually way too often lately to make sure it continued to work), but like this I have up to 1000 rows to sort through and I'm just not sure any more.

Some input would be appreciated. Thank you very much.

Edit: The majority of the operations will be 'SELECT', followed by UPDATE/INSERT ... ON DUPLICATE KEY UPDATE respectively (the later is for the relational approach)

migrated from stackoverflow.com Jul 3 '15 at 23:20

This question came from our site for professional and enthusiast programmers.

  • 1
    In general many rows are faster to deal(insert,update,delete) with in a db than many columns even if the size is much bigger. – Mihai Jul 3 '15 at 6:35
  • @Mihai The majority of the operations will be SELECT, does it apply to SELECT too? – Refugnic Eternium Jul 3 '15 at 6:37
  • 1
    @Mihai Which is exactly why I'm attempting to redesign the database now, before we actually get a problem. A lot of work to be sure, but if I do it right, I'll never have to touch it ever again. Here's hoping. :) – Refugnic Eternium Jul 3 '15 at 6:49
  • 1
    The question is really about table composition, or rather, table decomposition. The subject of table decomposition usually comes up in discussions about normalization. Is your question really about whether normalizing speeds up or slows down database operations? – Walter Mitty Jul 3 '15 at 10:14
  • 1
    EAV <> normalized – Neil McGuigan Jul 3 '15 at 16:14
1

MySQL has limits, such as 1017 columns in an InnoDB table (as of 5.6.9).

If you are not filtering or sorting on most of the columns, put them in a JSON string and put that string in a TEXT (or maybe MEDIUMTEXT) column.

A key-value store can lead to lots of JOINs. The limit is 61.

I'm confused -- Do you have 1000 sets of 7000 parameters? Or what? If it is a single set of parameters, then a single key-value table makes some sense.

  • I beg your pardon for not responding for so long. To answer your question, a single parameter set has 1000 columns and I have 7000 of those as of right now (number growing). And each time a new parameter needs to be added, it needs to be added as a column, meaning that all the datasets get that parameter added, regardless of whether they need it or not. Putting it into a text-column instead of 1000 columns is an interesting idea though. – Refugnic Eternium Jul 6 '15 at 5:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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