Use just one column vs use another column for performance

I have a doubt (probably very basic) when it comes to know if a field has been stored with information in the database.

I’m working with Laravel and MySQL as DB engine.

The situation is as follows:

I have a field in the database that stores information (in this case it is a LONGTEXT field with a large amount of information in it). This field stores information in an automated way (by means of a CRON).

When listing the information related to the records of that table, I need to know if the field in question contains information or not.

At first I had thought of including another field (column) in the same table that tells me if the field is empty or not. Although I consider that this would be a correct way to do it, on the other hand I think that I could save this column by simply checking if the field in question is empty or not. However, I’m not sure if this would be the right way to do it and if this could affect the performance of the application (I don’t know how MySQL does exactly this comparison or if it could be optimised by making use of the new field).

I hope I have explained myself correctly.
Schematically, the options are:

Option 1:

  • Have a single field (very large amount of information).
  • When obtaining the list with the records, check in the corresponding search if the field in question contains information.

Option 2:

  • Have two fields: one of them contains the information and the other is a boolean that indicates if the first one contains information.
  • When obtaining the list of records, look at the boolean.

The aim of the question is to use good practices as well as to optimise both the search and minimise the impact on the performance of the process.

Thank you very much in advance.

Source: Laravel

Leave a Reply