0

I am currently working on the redesign of a mysql bdd. This database is actually a huge table of 200 fields, several million lines, almost no index... In short, disastrous performances and huge ram consumption!

I first managed to reduce the number of fields by setting up 1:n relationships.

I have a question on a specific point: A number of fields on this database are optional and rarely filled in. (sometimes almost never) What is the best thing to do in this case?

  • leave the field in the table even if it is very often of null value
  • set up a n:n relationship knowing that these relationships, if they exist, will only return one line
  • ...or another solution I haven't thought of

Thank you in advance for your wise advice;) Dimitri

Cupidme
  • 1
  • 1
    `NULL` values may take up less or no space, [see here](https://stackoverflow.com/questions/6312215/mysql-how-much-space-does-a-null-field-use). Other than this, normalizing your table, as you are already doing, is probably the best improvement. – Tim Biegeleisen Apr 25 '19 at 07:56
  • Thank you for your advice. I don't really care about disk space. The main purpose of that refactoring is to improve performances especially when doing Select queries. – Cupidme Apr 25 '19 at 09:58
  • Then look into normalization and setting up appropriate indices. – Tim Biegeleisen Apr 25 '19 at 10:04

1 Answers1

1

My suggestion:

  • First of all, make sure to normalize your db. At least to 3rd Normal Form. This will probably reduce some of your original columns and split them over several tables.
  • Once that is done, in case you still have lots of 'optional and rarely filled' columns in some of your tables, consider how to proceed depending on your needs: What is most important to you? Disk space or Performance?

Take a look at MySQL Optimazing Data Size for extra tips/improvements for the re-design of your database ...depending on your needs.

Regarding 'set up a n:n relationship...' (I figure you meant 1:1 relationship) can be an interesting option in some cases:

In some circumstances, it can be beneficial to split into two a table that is scanned very often. This is especially true if it is a dynamic-format table and it is possible to use a smaller static format table that can be used to find the relevant rows when scanning the table (extracted from here)

MarcM
  • 2,173
  • 22
  • 32
  • Many thanks ! The most important by far is performance improvement. I would act on a case-by-case basis for these optionnal columns. – Cupidme Apr 25 '19 at 10:00
  • Then work hard on normalization, and then in defining right indexes. And don't worry if some 'optional and rarely filled' columns remains. – MarcM Apr 25 '19 at 10:08