Kjartan Mannes

"Why all do all your MySQL table definitions include NOT NULL all over the place"

Each column in MySQL saves one byte if it doesn't have to concider the NULL value.

"But that is insignificant? Even with 10000 rows you have only saved 10000 bytes which is 9-10 KB. Why bother?"

Ahh, but you do the math wrong. It is per column. On average our tables have 15 columns. That is 15 bytes saved per row, or 150000 bytes, which about 145KB.

"That doesn't really matter much though? You have to have a lot of rows before it becomes an issue. We have plenty of disk space"

Yet again the thinking is too narrow. Databases are not just saved in disk, they are loaded into RAM. How else do you think MySQL accesses them so fast? We have a limit on much RAM each MySQL process can use, and we generally have 100 MySQL proccess active. Even with 10 rows it starts to add up. 10 rows * 15 columns * 100 proccesses = 15000 bytes saved. We have thousands of rows. Of course this isn't fully real as what MySQL caches is a bit differnet, but we want the databases as compact as possible. Also disabling NULL means our queries and applications have one less value to worry about, which can add up to precious seconds when processing.

Also any simple things you can do early on to optimise you should go ahead and do, but only if they are simple and don't take any time to perform. I know people say to optimize as the last step, but in real life the last step is usually done under intense time pressures and high stress. Some optimization has to be done during the process to make sure the application isn't a mess at the end of the development cycle.

"So why does NULL even exist?"

It has uses. Concider keeping track of the age of people. If you don't know the age you don't want to record 0 as that will tamper with the data in case you need the average date. In MySQL AVG(age) will ignore NULL values. Sure if everyone is at least 18 you can do AVG(age) WHERE age > 18, but then your query is more compled and might take longer to run. So use NULL when you need it to perform queries on the data.

"Thank you master."