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

[Rick James]

String searches are not slow.

Make a parallel table. OK, so you are already doing that. The table contains meta information about the image, including the hash.

The problem comes when the hash index on the meta table is too big to be cached in RAM. Hashes are 'random'. A check to see if the hash is already in the table will need to do a disk access. That is, with under, say, 50M images, your checks are essentially CPU-bound, and you can probably check more than 1000 per second. With a billion images, the checks will slow down to something like 100 per second.

MD5 is a 128-bit hash, good enough for anything short of identifying each distinct atom in the universe. The hex version could be put into a BINARY(32) field, not VARCHAR(32). Note I say BINARY since you don't need any collation. And you don't need VAR since it is a constant length. Or it could be put into BINARY(16). This would shrink the index size.

How many images do you have?