MySql number of columns and parameter size affect on performance

Question

I have looked at similar questions such as this one Does number of columns affect MYSQL speed?

I will try to ask my question with more specification as it is slightly different.

I have the option of having a table like this;

UserID | Parameter 1 | Parameter 2

The table will NOT have any relational SQL performed on it. The queries will only read/write Parameter 1 OR Parameter 2 for a group of UserIDs.
Parameter 1 will be required more than 10 times as often as Parameter 2

Parameter 1 will store a data string of up to 1000 characters
Parameter 2 will store a data string of up to 100000 characters

I have a choice of format for these so I think TEXT(1000) or Blob for Parameter 1 and MEDIUMTEXT(100000) or MEDIUMBLOB for Parameter 2 would be valid. Note I just need to read/write these - I am not sure what the format choice would do to DB performance (minor question).

My main question is this; given the frequency of access to Parameter 1 and Parameter 2, the independence of the Parameters, and given the much greater size of Parameter 2, should I have one table;

UserID | Parameter 1 | Parameter 2

OR two tables;

UserID | Parameter 1 
UserID | Parameter 2

This is an edit to make things clearer. I am interested in the performance difference of the two table options given the previous description. I am especially concerned that the inclusion of the larger and less used Parameter 2 will have a significant impact on the performance when accessing Parameter 1. You may consider that the effect if there are 1000s of rows.

suspectus · Accepted Answer · 2013-05-31 16:16:04Z

3

Premature optimization. Go with whichever is easier from a programming viewpoint.

Obsessing over a little detail like this will just cause you to lose time you could actually be implementing your program.

edited May 31, 2013 at 16:16

suspectus

17.4k8 gold badges56 silver badges63 bronze badges

answered May 31, 2013 at 12:28

barryhunter

21.1k3 gold badges32 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Niccolo Over a year ago

There is little difference in programming as DB calls are dependant on the Parameter needed, certainly no difference in difficulty. Poor planning can lead to later failure or having to rewrite. I am happy to get this correct now rather than return to it.

barryhunter Over a year ago

Considering there almost certainly isnt a 'correct' answer, its kinda mute. Both have some pros and cons, there is no clear winner. If there was you would already have chose it! If you want someone to choose for you, use the single table and be done with it.

Niccolo Over a year ago

Let me try to make this clearer I am interested in the performance difference of the two table scenarios given the previous description. "non-answers" when some feels the need to answer a different question to the one that was asked are just time wasters. If you don't know the answer or you don't want to answer then please just don't answer or divert the question to some other discussion.

Collectives™ on Stack Overflow

MySql number of columns and parameter size affect on performance

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related