SQL optimization (mySQL) with BOOLEAN values

Question

I am working on a side project, which is a pretty serious matter; my question is about the efficiency obtained by using the BOOLEAN value to determine if additional data processing is needed.

For example: if I had a table listing all the creatures. Another table, which was relational in nature, indicated the period of their hibernation and the calories consumed every day during hibernation.

Is it effective to have a value inside the table (Creatures) for the "sleeping" BOOLEAN.

If true, go to the "hibernation_creature_info_relations" table and find the creature with this identifier and return this information.

This means that for all creatures whose value for "hibernates" = false, SQL will not allow searches on the large table "hibernation_creature_info_relations."

Or, when using the identifier, the process is checked so quickly in the hibernation_creature_info_relations table so fast that it will actually have a greater impact on performance if you process the argument that based on whether the value of the sleep mode is set to true or false?

Hope this was enough information to help you understand what I am asking, if not, let me know so that I can rephrase or include more detailed information.

Answer 1

No, this is not the best way to do something.

Use a normal field, which may be null .

Example

 table creatures --------------- id name info_id 1 dino null 2 dog 1 3 cat 2 table info -------------- id info_text 1 dogs bark 2 cats miauw 

Now you can simply complete the connection:

 SELECT c.name, i.info_text FROM creature c LEFT JOIN info i ON (c.info_id = i.id) 

If you do, SQL can use an index.
No SQL database will create an index in a logical field.
The power of this field is too low, and the use of indexes on fields with low power slows down, rather than speeds up.

See: MySQL: low power / selectivity columns = how to index?

Answer 2

If you want to use the "hibernates" column only so that SQL cannot search through another table, you must follow @Johan , otherwise you can create an index in the "hibernates" column to improve the runtime. But keep in mind that @Johan is trying to tell you.