Re: Fastest way to check for an image in a table

[Rick James]

FULLTEXT won't help.
Having a compound index won't help.
BINARY(16) instead of BINARY(32) with hex won't matter much.

The Hash is virtually guaranteed to be unique. Adding on the length won't help performance any. (It will make the index bigger.) A BTree is a BTree; the traversal of a BTree is mostly a function of its depth.

A BTree with 5 million entries will be about 4 levels deep. That is, it will fetch the root block, search it. That will point to a block at the next level. Repeat until reaching the bottom level. At this leaf level will be a pointer to the row. Now go fetch the row from the data.

That adds up to hitting about 5 blocks (4 in index, 1 or more in the data). But, based on my math, the 4 index blocks (in fact the entire index) is likely to be cacheable. That is, No disk I/O, at least for the index.

If you have 5 billion images instead of 5 million, then I would be pushing for the details you are asking about. Or if you needed to process more than 1000/second. For 5M, these questions are very minor.
Numeric vs String? Not worth worrying about. The bigger issues are (1) disk I/O, and (2) overhead of traversing a BTree.

If everything necessary is cached, one row out of 5 million will be found in something like a millisecond. If not all cached, add 10ms per disk hit. A 'cold' system (right after restart) will need about 5 disk hits -- so 50ms. Quickly the top layers of the BTree will get cached.