The performance benefit to be had with NOT NULL columns comes from the fact that a NOT NULL column can be of fixed length (allowing for fixed length records). Using NOT NULL probably wont offer any benefit on a VARCHAR column, since VARCHAR columns are not of fixed length to begin with.
-JF > -----Original Message----- > From: Randy Chrismon [mailto:[EMAIL PROTECTED] > Sent: Tuesday, September 16, 2003 6:59 AM > To: [EMAIL PROTECTED] > Subject: Re Does NULL == ""? > > > All this discussion about the definition of NULL and its use in > database querying has been most interesting and enlightening. I > hadn't realized I was asking such a deep question. Unfortunately, I'm > still at the stage of designing tables where I KNOW I will be loading > thousands of records that, more often than not, contain anywhere from > 10% to 50% columns where no values have been entered for whatever > reason (exporting an existing Lotus Notes database to MySQL). > Naturally, that 10-50% will never consist of precisely the same > columns from one record to the next. > > Putting aside issues about the distinctions among NULL, 0, and "", my > question is this: At the end of the day, which system is going to be > easier to export to and which will be faster (produce result sets > faster) when all the data are loaded? There are three formats for the > DDL that I can think of immediately (I'm a newbie at this stuff): > > 1. CREATE TABLE My_Table( > First_Field VARCHAR(10), ..., ...) > > 2. CREATE TABLE My_Table( > First_Field VARCHAR(10) NOT NULL, ..., ...) > > 3. CREATE TABLE My_Table( > First_Field VARCHAR(10) NOT NULL Default '', ...,...) > > As I read it, each of the above results in a different table. (BTW, > it > looks like using the MySQLCC GUI table builder results in format 3.) > The MySQL documentation says that tables with non-nullable columns > yield better performance. What does that mean if you have all NOT > NULL > columns (assume for discussion that everything is a character), many > of which contain "" as a value? > > Randy > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
