Best unofficial Apache Server developers community
Username
Forgot password?
Sign in with Twitter account
Sign in with Facebook account

Few questions on planning mysql table

⇧
1
⇩
56 views

  1. say I have a large table, 200 000 rows and I need to change/add column of enum type, would it work properly or would I experience problems? Should I avoid enums? example is hair color: say I have black and brown, but 200 000 rows later I decide to add blonde. (these are the fields that there will be lot of where conditions, looking up members)

  2. should email field be varchar(255)?

  3. Should IDs be always unsigned bigint?

I'm basically trying to optimize a thing or two.

EDIT: I anticipate having no more than 300 000 users, I just wonder if mediumint vs int or bigint for user ids and related ids in other tables would have any noticeable performance gain?

mysql
asked January 11, 2011 6:30 am CST
BugBusterX
posted via StackOverflow

2 Answers

⇧
0
⇩
 

  1. enum is not a bad idea for limited set of choices like hair color, and frequent update to small table (like 200k rows) is not painful

  2. the maximum length of an email address is 320 characters. - source

  3. Unsigned Yes, if you want auto_increment, unsigned is the way to go Big int? if your table is less than 1 millions, you can set much lower than it

answered January 11, 2011 7:23 am CST
ajreal
⇧
0
⇩
 

  1. 200,000 is not really that large. You shouldn't have a problem performing an ALTER TABLE on a table that small.

  2. That's what I normally use

  3. I presume you mean primary keys - unsigned bigint will allow you pretty large numbers for your primary key. Obviously using a larger data type will take up more disk space but at only 200,000 records it is not really noticeable at all.

answered January 11, 2011 7:23 am CST
matthewh

Your answer

Join with account you already have

Sign in with Twitter account
Sign in with Facebook account
Sign in with Google Friend Connect

Preview
Similar questions
Displaying questions & answers from mysql database with php into a html form in the frontend
December 28, 2010
Gzip - questions about performance
December 20, 2010
Mod_rewrite and Apache questions
January 10, 2011
Im making a PHP game with a map, and i have some questions
January 10, 2011
Questions on sqlite transactions
December 27, 2010
Populating a table with a large amount of data from a temporary table - MySQL
January 5, 2011
MYSQL inserting records form table A into tables B and C (linked by foreign key) depending on column values in table A
January 7, 2011
Spring3, JAXB2, Java6, NamespacePrefixMapper questions
January 4, 2011
How to write a .htaccess redirect like stackoverflow does for its questions
January 3, 2011
Solr questions regarding handler resolution and escaping
January 9, 2011
Can I change the datatype for the "Create Table" field in the Show Create Table function in MySQL?
January 10, 2011
PHP / MySQL: How to call a col in a MySQL-Result with more than one table?
January 9, 2011
How do I workaround this error ? You can't specify target table my table for update in FROM clause
January 9, 2011
Insert into table from temporary table takes a long time
January 10, 2011
Mysql two table query
December 30, 2010
How to get the ID of the last inserted row in the table in MySql with PHP
January 5, 2011
Mysql right to use myisam for this table?
December 30, 2010
MYSQL - SELECT ALL FROM TABLE if...
January 5, 2011
How to join my mysql table
January 6, 2011
MySQL, Table with two dates?
January 7, 2011
How do i retrieve table id inside mysql
January 4, 2011
Update all table rows but top N in Mysql
January 9, 2011
Problem to insert into mysql table
December 30, 2010
MySQL table type to use, innodb?
December 28, 2010
MySQL delete in table problem
January 5, 2011
Default value in mysql table id.. invoice help
January 9, 2011
How to retrive only one cell from MySQL table with PHP
January 1, 2011
I need some MySQL lookup table advice
December 20, 2010
MySQL - Aren't these privileges enough to create a table?
January 3, 2011
Mysql changing values in table?
January 7, 2011