Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've inherited a database that uses sets of 32-byte hex strings to attempt to uniquely identify objects. Each string is called a fingerprint and each object can have up to 20 fingerprints.

I'm migrating the database across servers and I've noticed that re-populating the Fingerprint table takes hours. It seems that each fingerprint is given an integer id and is stored as a varchar, there's an index on the fingerprint column and hence the slow inserts.

My question is: is it worth my while to refactor this design and remove the Fingerprint table altogether? I would guess that indexing on numbers rather than strings would be faster, is this correct?

If the design looks like this: (hopefully this make sense)

Object(1,*)<----->(*,*)ObjectFingerprint(*,*)<------>(1,*)Fingerprint
 id                      object_id                          id
                         fingerprint_id                     fingerprint

Would it be better to use:

Object(1,*)<----->(*,*)Fingerprint
 id                     object_id
                        fingerprint

I'm using a MySQL server with INNODb tables and I have roughly 1.4 million objects.

share|improve this question
add comment

1 Answer 1

up vote 4 down vote accepted

I would recommend using the BINARY type; strings waste a lot of space, and integers require conversion.

http://dev.mysql.com/doc/refman/5.0/en/binary-varbinary.html

For the other part: why do you need the associative table? If objects and fingerprints are 1 to 1, you could either store them directly in the object table, or just have single table with the id, fingerprint, and object id FK.

share|improve this answer
    
Interesting, thanks. When you say that integers require conversion do you mean from existing hex string to integer or from hex value to whatever type is used to index? Will BINARY indexes and comparisons be much faster than VARCHAR? –  WilliamMayor Jun 30 '11 at 13:50
    
Sorry--I didn't entirely answer your question. The BINARY is not necessarily faster than char, but could be since it occupies half the memory. The conversion I referred to was if you were to store the fingerprints as ints; you would have hex->decimal to store, and decimal->hex for retrieval. –  Bryan Agee Jun 30 '11 at 15:19
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.