6

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.

Improve this question

1 Answer 1

Reset to default
5

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.

Improve this answer
2
  • 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, 2011 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, 2011 at 15:19

Your Answer

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

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