CODEWITHSUNDEEP
mysqlsqloptimization
user856197
user856197

Reputation:

SQL (mySQL) optimization via BOOLEAN values

I am working on a side project that is quite an undertaking; my question regards the efficiency gained when using a BOOLEAN value to determine whether or not further data processing is required.

For example: If I had a table that listed all the creatures. In another table that was relational in nature listed their hibernation period, and calories consumed each day during hibernation.

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

If true then go to the "hibernation_creature_info_relations" table and find the creature with that ID and return that information.

This means that for all the creatures whose value for "hibernates" = false will prevent SQL from having to search through the large table of "hibernation_creature_info_relations."

Or when using ID's is the process so fast in checking the "hibernation_creature_info_relations" table so fast that there will actually be a larger impact on performance by having to process the argument of doing what based on if the value of hibernation is set to true or false?

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

Upvotes: 4

Views: 666

Answers (2)

Yaqub Ahmad
Yaqub Ahmad

Reputation: 27659

If you want to use the column "hibernates" only to prevent the SQL from having to search through the other table then you should follow @Johan otherwise you can create index on the column "hibernates" it will improve the execution time. But keep in mind what @Johan is trying to tell you.

Upvotes: 0

Johan
Johan

Reputation: 76597

No, that is not a good way to do things.

Use a normal field that can be null instead.

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 just do a join:

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

If you do it like this, SQL can use an index.
No SQL database will create an index on a boolean field.
The cardinality of that field is too low and using indexes on low cardinality fields slows things down instead of speeding things up.

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

Upvotes: 4

Related Questions