CODEWITHSUNDEEP
mysql
gio
gio

Reputation: 975

Mysql: Best type for this non-standard ID field

I have a db of people ( < 400 total ), imported from another system. The IDs are like this

_200802190302239ILXNSL

I do queries and joins to other tables on that field

Because I'm lazy and ignorant about MySQL data types and performance, I just set it to Text.

What data type should I be using and what sort of index should I set on it for best performance?

Upvotes: 1

Views: 379

Answers (3)

MarkR
MarkR

Reputation: 63538

Set it as a VARCHAR with appropriate length; this way you can index the whole column, or use it as primary key or unique index component.

If you are really paranoid about performance AND you're sure it won't contain any non-ascii characters, you can set it as ascii character set and save a few bytes by not needing space for utf8 (in things such as sort-buffers and memory temporary tables).

But if you have 400 records in your DB, you almost definitely don't care.

Upvotes: 0

Marius Burz
Marius Burz

Reputation: 4645

Type: as said, varchar or char(way better if the length of this ID is fixed).
Index type: a UNIQUE probably (if you won't have multiple entries with the same ID)

As a further observation, I would probably hesitate (for performance reasons) to use this field as a natural primary key, especially if it will be referenced by multiple foreign keys. I would probably just create a synthetic primary key(for instance an AUTO_INCREMENT column) and a UNIQUE index on this non-standard ID column.

On the other hand, with less that 400 rows, it doesn't really matter, it will be smoking fast anyways, unless there are big/huge tables referencing this persons table.

Upvotes: 1

GolezTrol
GolezTrol

Reputation: 116110

varchar (or char, if they all have the same length).

http://dev.mysql.com/doc/refman/5.0/en/char.html

Upvotes: 3

Related Questions