0

Possible Duplicate:
NULL in MySQL (Performance & Storage)

Do you HIGHLY recommend to uncheck NULL allowed for a column in a table, if:

  • that column is not a key,
  • that column could be SELECTed or INSERTed into the table or UPDATEd, but never used in WHERE, join and other parts of any query (WHERE, JOIN etc. could be counted when we decide what column to index for better performance).

So, if you leave the checkbox "NULL" checked (i.e. NULL is allowed) for the column described above, will it affect the performance?

I hope the answer (at least for MySQL) is:

  • no, it will not affect even for 1%, or
  • no, it will not affect at all.
Community
  • 1
  • 1
Haradzieniec
  • 9,086
  • 31
  • 117
  • 212
  • Do not that *checking or unchecking* `NULL` is what you do in your MySQL client, probably phpMyAdmin. What you're actually doing is allowing the column to contain `NULL` values or not in the corresponding `CREATE/ALTER TABLE` statement. – Konerak Nov 14 '11 at 06:57

1 Answers1

1

Yes, I do recommend you make any column which does not need to be NULL, NOT NULL.

In my view, it is one of the "bugs" in SQL, that NULL is enabled by default.

The reason is nothing to do with performance, but for application correctness. If you have a column value which is mandatory, it makes more sense to make it NOT NULL (and indeed, a NULL value would be an error). Do use a DEFAULT value if it makes sense.

In short: No column should ever be nullable unless there is a valid reason within your database structure for it to be null.

Note also that NULLs do something "special" in unique indexes - several rows with a NULL are permitted.

MarkR
  • 62,604
  • 14
  • 116
  • 151