CODEWITHSUNDEEP
mysqlsql
Matthew Loucks
Matthew Loucks

Reputation: 56

MySQL multiple rows vs storing values all in one string

I was just wondering about the efficiency of storing a large amount of boolean values inside of a CHAR or VARCHAR

data
"TFTFTTF"

vs

isFoo  isBar isText
false  true  false

Would it be worth the worse performance by switching storing these values in this manner? I figured it would just be easier just to set a single value rather than having all of those other fields

thanks

Upvotes: 3

Views: 666

Answers (4)

Timo Strotmann
Timo Strotmann

Reputation: 391

This would be a bad idea, not only does it have no advantage in terms of the space used, it also has a bad influence on query performance and the comprehensibility of your data model.

Disk Space

In terms of storage usage, it makes no real difference whether the data is stored in a single varchar(n) or char(n) column or in multiple tinynt, char(1)or bit(1) columns. Only when using varchar you would need 1 to 2 bytes more disk space per entry.

For more information about the storage requirements of the different data types, see the MySql documentation.

Query Performance

If boolean values were stored in a VarChar, the search for all entries where a specific value is True would take much longer, since string operations would be necessary to find the correct entries. Even when searching for a combination of Boolean values such as "TFTFTFTFTT", the query would still take longer than if the boolean values were stored in individual columns. Furthermore you can assign indexes to single columns like isFoo or isBar, which has a great positive effect on query performance.

Data Model

A data model should be as comprehensible as possible and if possible independent of any kind of implementation considerations.

Upvotes: 2

Vince0789
Vince0789

Reputation: 361

Realistically, a database field should only contain one atomic value, that is to say: a value that can't be subdivided into separate parts.

Columns that do not contain atomic values:

  • cannot be sorted
  • cannot be grouped
  • cannot be indexed

So let's say you want to find all rows where isFoo is true you wouldn't be able to do it unless you were to do string operations like "find the third characters in this string and see if it's equal to "F". This would imply a full table scan with every query which would degrade performance quite dramatically.

Upvotes: 1

kbhargav04
kbhargav04

Reputation: 1

it depends on what you want to do after storing the data in this format. after retrieving this record you will have to do further processing on the server side which worsen the performance if you want to load the data by checking specific conditions. the logic in the server would become complex. The columns isFoo, isBar, and isText would help you to write queries better.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Don't do it. MySQL offers types such as char(1) and tinyint that occupy the same space as a single character. In addition, MySQL offers enumerated types, if you want your flags to have more than one value -- and for the values to be recognizable.

That last point is the critical point. You want your code to make sense. The string 'FTF' does not make sense. The columns isFoo, isBar, and isText do make sense.

There is no need to obfuscate your data model.

Upvotes: 8

Related Questions