MYSQL - int or short string?

Question

I'm going to create a table which will have an amount of rows between 1000-20000, and I'm having fields that might repeat a lot... about 60% of the rows will have this value, where about each 50-100 have a shared value.

I've been concerned about efficiency lately and I'm wondering whether it would be better to store this string on each row (it would be between 8-20 characters) or to create another table and link them with its representative ID instead... So having ~1-50 rows in this table replacing about 300-5000 strings with ints?

Is this a good approach, or at all even neccessary?

Guffa · Accepted Answer · 2013-03-08 18:58:08Z

2

Yes, it's a good approach in most circumstances. It's called normalisation, and is mainly done for two reasons:

Removing repeated data
Avoiding repeating entities

I can't tell from your question what the reason would be in your case.

The difference between the two is that the first reuses values that just happen to look the same, while the second connects values that have the same meaning. The practical difference is in what should happen if a value changes, i.e. if the value changes for one record, should the value itself change so that it changes for all other records also using it, or should that record be connected to a new value so that the other records are left unchanged.

If it's for the first reason then you will save space in the database, but it will be more complicated to update records. If it's for the second reason you will not only save space, but you will also reduce the risk of inconsistency, as a value is only stored in one place.

edited Mar 8, 2013 at 18:58

answered Mar 8, 2013 at 17:46

Guffa

703k111 gold badges760 silver badges1k bronze badges

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

1 Comment

Näbil Y Over a year ago

Well, I want to avoid repeating plus "minimizing" by using more ints and less strings... Those fields won't be edited often

Avitus · Accepted Answer · 2013-03-08 17:27:55Z

1

That is a good approach to have a look-up table for the strings. That way you can build more efficient indexes on the integer values. It wouldn't be absolutely necessary but as a good practice I would do that.

answered Mar 8, 2013 at 17:27

Avitus

16k6 gold badges47 silver badges54 bronze badges

Comments

Adam Plocher · Accepted Answer · 2013-03-08 17:29:00Z

1

I would recommend using an int with a foreign key to a lookup table (like you describe in your second scenario). This will cause the index to be much smaller than indexing a VARCHAR so the storage required would be smaller. It should perform better, too.

answered Mar 8, 2013 at 17:29

Adam Plocher

14.3k6 gold badges51 silver badges83 bronze badges

Comments

Markus Deibel · Accepted Answer · 2013-03-08 17:38:31Z

1

Avitus is right, that it's generally a good practice to create lookups.

Think about the JOINS you will use this table in. 1000-20000 rows are not a lot to be handled by MySQL. If you don't have any, I would not bother about the lookups, just index the column.

BUT as soon as you start joining the table with others (of the same size) that's where the performance loss comes in, which you can (most likely) compensate by introducing lookups.

answered Mar 8, 2013 at 17:38

Markus Deibel

1,35920 silver badges27 bronze badges

Collectives™ on Stack Overflow

MYSQL - int or short string?

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related