2

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?

Improve this question
2
  • instead of SET latin1 put SET binary Commented Dec 2, 2013 at 20:02
  • isn't 'id' BINARY(3)? Commented Dec 2, 2013 at 20:14

1 Answer 1

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

I don't have any issue with space. The table should be able to hold 500m rows. For that reason I need the fastest possible option. I CAN'T, change the primary key. My primary key MUST be small letters and numbers (so, 36 possible characters) and 20 characters in length. What's the best way to make this faster?
Because of the length of the key, I can store it as 2 bigints, somehow. Is this better than a char(20)?
@user3054967: To make what faster? Besides "searches", your question has not specified any particular operation. It's pretty difficult to advise how to make "this" faster without knowing what "this" is. As for whether 2 BIGINT columns is "better" than a CHAR(20) column, it's again impossible to say without knowing what on Earth you're trying to achieve: but I would note that it has the advantage of only needing 16 bytes instead of 20 (although 36^20 < 2^(8*13), so your data would fit in 13 bytes).
Database engine is InnoDB. I perform about 10-20/sec updates if the row exists, inserts if not. Also I read data, about 10-20/sec. The column sizes are small. I know optimization is not a simple matter, but I believe it should be simple to tell witch is faster: char(20), binary(20), 2 usinged long ints, used as primary index.
@user3054967: Then one presumes that 500 million rows is sufficiently far into the future for it to not be a concern you need expend resource on right now? It'd probably be wise to focus on more current problems.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.