Integer vs String field mysql design approach for php web

Question

I have a table with products that contains 2 fields.

1)id of the product int-7digits

2)name of the product

The name of the product can only be 27 different values like banana,apple etc

Now im wondering what is the best approach for my web-server resources. I mean i should use 1,2,3,... values or string values?

~Edit my table will be about 1million rows and steadily increasing over time.

Now im wondering what is the best approach for my web-server resources. I mean i should use 1,2,3,... values or string values? in which field... product name ?? — Hiren Pandya
– Hiren Pandya, Commented Apr 22, 2013 at 9:50
The approach depends upon the usage of the field.. Whether you want to fetch the value and print it to somewhere or just need some comparison or similar kinda thing.. hope you got the point.. — Hiren Pandya
– Hiren Pandya, Commented Apr 22, 2013 at 9:51

Glitch Desire · Accepted Answer · 2013-04-22 09:56:54Z

1

You should use VARCHAR for the name field, for readability. If you have to come back to this in 9 months and remember if 1 is an apple or a banana, you're going to struggle a lot.

As I assume you have to print out product names, you're basically choosing between selecting strings in PHP or MySQL. Either SELECT * FROM fruit WHERE product_name = 'Banana' or SELECT * FROM fruit where product = 7 and then foreach($fruits as $fruit): if $fruit['id'] = $row['product'] { return $fruit['name']; } endforeach;

Assigning values from a PHP array array(1 => 'apple', 2 => 'banana' ... 27 => 'eggplant'); is going to use more overhead than a MySQL SELECT statement on a VARCHAR field, as well. Any performance you gain on the database side will be lost fivefold on the application side.

edited Apr 22, 2013 at 9:56

answered Apr 22, 2013 at 9:50

Glitch Desire

15.1k7 gold badges46 silver badges55 bronze badges

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

3 Comments

Incognito Over a year ago

Readability in not my issue here, its somewhat correct but this will be one time job so i wont be reading the values but the server will do. Will i have any actual benefit on my resources?

Glitch Desire Over a year ago

@Incognito MySQL is a lot faster at performing SELECT than PHP will be on assigning strings via an int lookup in an array. Any performance you gain on SQL side will be lost fivefold on PHP side. (Though, you are right to think integer comparison is faster than string comparison)

Incognito Over a year ago

Thanks a lot for the answer, you are right it will be slower as the php will have to assign the values.

Community · Accepted Answer · 2017-05-23 11:57:18Z

Use VARCHAR.

Alternatively, if you are absolutely certain the products will stay static, you could use ENUM to save some space, but there won't be much space to save on only 27 rows.

However, if you need to reference the products from other tables, consider creating a surrogate key (auto-incrementing integer), to save space in the referencing tables (compared to repeating VARCHAR product names or 7-digit product IDs).

NOTE: To "save space" is not just about storage space - smaller data means less I/O and more effective caching with the given amount of RAM. This is not important for small tables, but can make a difference on large ones (e.g. if you have a big "order items" table that references products).

Collectives™ on Stack Overflow

Integer vs String field mysql design approach for php web

2 Answers 2

3 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Related