Storing column for data that doesn't exist for majority of rows

All we need is an easy explanation of the problem, so here it is.

I’m using mariadb.

I have a single table that keeps track of video_count and image_count for a few different sites. I.e.

 type        | enum('SITE1','SITE2','SITE3')                                
 video_count | int(11)                                                
 image_count | int(11) 

video_count will exist for ALL sites but image_count only exists for SITE3. Also, SITE3 will be a minority of rows.

So does it make sense to arrange the data this way on one table?
I’d like to query ALL rows…but is it a problem if it’s redundant and expensive knowing that 90% of my rows will have 0 for image_count?

It’d be perfect if I could do something like SELECT * from TABLE BUT DON't GET video_count WHERE type != 'SITE3'"

Is that even a possible query in SQL? Or does it now make sense to create separate tables for ‘SITE1…SITEN’? Ty!

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I recommend storing NULL in the image_count column on rows where it is inapplicable because the type doesn’t need it. NULL is different from an integer 0.

InnoDB doesn’t store a value in the row for a NULL. So it’s pretty low overhead to leave the column NULL on the rows that don’t need it. There’s a bit stored in the row header for each nullable column, but that will be needed whether you store a value or not.

10k rows is small for an InnoDB table. I wouldn’t worry about it unless you need to run this database on a Raspberry Pi or similarly compact platform.

SQL does not have any syntax for SELECT * except-the-columns-I-don't-want. If you use SELECT *, that means all the columns, including those that are NULL. If you want a subset of columns, you have to name all of the ones you want.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply