0

I am using MYSQL as database. Check is this table definition

CREATE TABLE `test`.`header`
( 
`header_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, 
`title` VARCHAR(500) NOT NULL, 
`body` VARCHAR(5000) NOT NULL, 
`created_by_id_ref` BIGINT UNSIGNED NOT NULL, 
`created_date` DATETIME NOT NULL, 
`updated_date` DATETIME NULL DEFAULT NULL, 
`is_void` TINYINT(1) NULL DEFAULT NULL,
PRIMARY KEY (header_id`) ) ENGINE=INNODB CHARSET=latin1 COLLATE=latin1_swedish_ci;

In my interface user can delete any of the record by simply selecting the record from a grid view. So in that case I am simply updating the "is_void" status to true.

I declared that column by this syntax. which shows above. Here it again.

`is_void` TINYINT(1) NULL DEFAULT NULL,

So If I add an index to this column is this column declaration is good? In that case records default have null values. For voided records it will be "1". So if I am going to filter any of those records for voided records I have to use

Select ........ where is_void=1;

If I want to filter non voided records I can use 

Select ........ where is_void IS NULL;

So is this NULL declaration affect to my select query performance? (Remember I indexed this column)

Or Shall I declare my column as

`is_void` TINYINT(1) NOT NULL,

and then I have insert "0" for non voided records. Then if I want to filter non voided records I can use 

Select ........ where is_void=0;

So What is the best? where is_void=0; or where is_void IS NULL;

Thank you very much.

Prageeth Liyanage
  • 1,612
  • 2
  • 19
  • 41

1 Answers1

0

In terms of performances, both approaches are equivalent*.

In terms of logic, NULL is widely regarded as meaning "unknown value" or "not applicable". Go with 1 or 0, as defined by the TRUE and FALSE constants.

Also, even though MySQL implements it as an alias for TINYINT(1), I would advise using the ANSI-standard BOOLEAN type.

* Ok, this is not entirely true in theory.

Community
  • 1
  • 1
RandomSeed
  • 29,301
  • 6
  • 52
  • 87
  • Thank you RandomSeed. I declared that column to null to save the storage space. Because I read that null values will need only 1 bit to store that NULL condition other values need more storage. IN my tables void is not a thing that frequently occur.So I thought that instead updating 0 for every unvoided record why shouldn't I update 1 for only voided records. what to do you think ? – Prageeth Liyanage Oct 02 '14 at 10:09
  • I think that 100 million boolean values occupy around 100 MB of storage, and you must have better things to think about. And you will probably never have to "update every unvoided record". This is likely to be a one-time operation that will occur when you create the column. – RandomSeed Oct 02 '14 at 10:22