CODEWITHSUNDEEP
mysql
user3054967
user3054967

Reputation: 25

MySQL Binary Type Usage example

I have a 20 character 

'id' CHAR(20) NOT NULL CHARACTER SET latin1,

Can I use the Binary data type:

1) in order to save space

2) speed up searches, as the id is the primary index?

If yes, how? Do i need to convert my id to hex?

Upvotes: 2

Views: 11397

Answers (1)

eggyal
eggyal

Reputation: 125865

MySQL's BINARY data type, like CHAR, holds fixed-length strings. You still have to specify the width of the column, e.g. BINARY(20). The only difference is that MySQL treats the data within the column as raw bytes rather than encoded text.

Assuming that you maintain the same data within the column, but merely change the column's datatype to BINARY(20) instead of CHAR(20) CHARACTER SET latin1, then:

  1. in order to save space

    No. Since latin1 is a one-byte character set, both data types will occupy 20 bytes per field. See Data Type Storage Requirements.

  2. speed up searches, as the id is the primary index?

    Not really. The only difference will arise through the additional time required to perform comparisons under your chosen collation. If this is truly of concern (and it very much should not be), you could simply use the latin1_bin collation instead.

  3. Should the column actually contain text data, you will no longer benefit from MySQL's automatic transcoding and application of collations: so operations depending on such behaviour will either need to explicitly ask that of MySQL (losing the benefits of indexing) or else perform it within the application layer (could prove very expensive indeed).

Are you actually suffering from a shortage of storage space, or slow searches? If so, perhaps you should profile your storage/queries accordingly in order to determine where resources are being hogged; otherwise, I am reminded of Knuth's maxim: premature optimisation is the root of all evil.

If after investigating you discover that your problem does indeed lie with the length of your primary key, you might instead consider using a surrogate value: e.g. MySQL's SERIAL data type.

Upvotes: 2

Related Questions