3

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!

7
  • 1
    You seem to be engaging in premature optimisation. The overhead of maintaining and having to query multiple tables will be worse.
    – mustaccio
    May 27 at 19:49
  • Yeah, I'd like to avoid multiple tables. So seems like it's not much to worry about? But say for example I'm getting a ton of unnecessary image_counts..will this lead to scalability issue...(seeing as it's redundant knowing they'll always be 0) May 27 at 19:52
  • They don't usually measure data volume and transaction performance in tons, so I don't know how much that is. However, speaking about scalability, at the scale this "issue" starts to move the performance needle, you'll be worried about other, more important things.
    – mustaccio
    May 27 at 19:57
  • 1
  • 1
    Is type the PRIMARY KEY??
    – Rick James
    May 28 at 18:39

1 Answer 1

2

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.

3
  • I MODIFIED the COLUMN to DEFAULT to NULL, ty May 28 at 18:14
  • Count of 0 is not the same as NULL (as you yourself say), so you can't substitute one for the other. The count of 0 reflects the fact that no images exist; the NULL count indicates that we don't know if images exist or not.
    – mustaccio
    May 28 at 19:42
  • @mustaccio, Yep, that's correct. NULL is also used when the datum is inapplicable. I understood the OP's situation to be that the image count is not relevant to SITE3. May 28 at 20:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.