regdude Posted June 28, 2009 Share Posted June 28, 2009 Hi, I have been googling for this question for a while now and I found few answers for few situations. Now my IP is stored as INT, not char, and seems to be bit faster and I encountered with a new atribute - UNSIGNED. So the unsigned atribute can't store negative numbers, but what happens if an UPDATE or INSERT gives a negative number? What should be the best type and size for a number with 10 digits (positive), the best for numbers 1 or 0 and the best for a text with about 50 chars? Quote Link to comment https://forums.phpfreaks.com/topic/163973-solved-best-types-and-sizes/ Share on other sites More sharing options...
dzelenika Posted June 28, 2009 Share Posted June 28, 2009 If your table don't contains millions of rows it is irrelevant. I prefer using data format which is easier to handle (VARCHAR for IP address, INT for integer numbers CHAR(1) for yes/no, status, active or similar values and off course VARCHAR for text Quote Link to comment https://forums.phpfreaks.com/topic/163973-solved-best-types-and-sizes/#findComment-865027 Share on other sites More sharing options...
regdude Posted June 28, 2009 Author Share Posted June 28, 2009 The table is quite large, it has about 50 colums and over 1000 rows (will be larger), so every milisecond counts. Quote Link to comment https://forums.phpfreaks.com/topic/163973-solved-best-types-and-sizes/#findComment-865030 Share on other sites More sharing options...
fenway Posted June 28, 2009 Share Posted June 28, 2009 The table is quite large, it has about 50 colums and over 1000 rows (will be larger), so every milisecond counts. Speed has nothing to do with column type choice. What happens with an out-of-range value depends on your mysql version and your choice of sql mode. Storing IP addresses requires INT UNSIGNED. 10 digits ~ 10M; MEDIUMINT UNSIGNED goes up to ~16M, but I'd stick with INT UNSIGNED regardless. 0/1 can use a BIT field, though I'm always against storing just a binary flag, since that's NEVER sufficient. As for 50 (single-byte) characters, your choices are CHAR(50) or VARCHAR(50), which really depends on your data distribution. Quote Link to comment https://forums.phpfreaks.com/topic/163973-solved-best-types-and-sizes/#findComment-865047 Share on other sites More sharing options...
regdude Posted June 28, 2009 Author Share Posted June 28, 2009 Well, I would like to doubt that. A while go I made the whole script in a total mess, but a week ago I did a major recode. What I did was simply change from checking name to checking ID and some other things, like shortening the code (less instructions), I managed to get the query time from 1.2s to 0.2s! After you gave me the bit-type to test, I found in google an article "why I shouldn't use BIT", so I better will not. So what should be better? Can I specify and action for an out-of-range value on a single column? Or that is only possible with every column? What is the default action? Quote Link to comment https://forums.phpfreaks.com/topic/163973-solved-best-types-and-sizes/#findComment-865096 Share on other sites More sharing options...
fenway Posted June 28, 2009 Share Posted June 28, 2009 Sure, finding numbers is faster than finding text -- but that's not column type choice now, is it? That's application logic in deciding which field(s) to search and/or index. And I too recommended against BIT -- but only because it's probably going to change later. If you know for certain it will only ever store 0 and 1, then BIT *is* the correct type. As for out-of-range, mysql will either issue a warning or an error, depending on the sql_mode. How you decide to handle those is your choice... but there's nothing more mysql can do for you. Quote Link to comment https://forums.phpfreaks.com/topic/163973-solved-best-types-and-sizes/#findComment-865124 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.