which hash function to choose for indexing uuid string on mysql? md5,crc64 or fnv

15
April 16, 2019, at 04:10 AM

I have a column store the uuid string. I add a new column to store its integer(64bits) hash value for indexing. which hash function to choose?

1. int(md5('a306d9cb-4d75-4673-ae43-700470692521').hexdigest()[-16:], 16)
2. crc64('a306d9cb-4d75-4673-ae43-700470692521')
3. fnv.hash('a306d9cb-4d75-4673-ae43-700470692521', bits=64)

it`s a mapping table for third party account.

table example:

openid: o00HW1KP16EvXs5whqPH2qyx667s
appid: wxb25520ae9512cabb
uid: 135435123

(openid, appid) --> uid

used to create a compound index on (openid, appid). Can make it faster if add a integer column like hash(openid, appid)?

Answer 1

It's not necessary to hash a UUID for indexing it. You can store a UUID in a CHAR(36), or else convert it to BINARY(16) to make it more compact. You can make an index on a column of either of these data types.

Also, creating an integer hash from the UUID will remove most of the bits of information from the UUID, making it more likely to find duplicate values. You won't be able to put a unique constraint on the hash, nor make it the primary key.

So there's no need to index it, and it can only spoil the benefit of using the UUID in the first place.

READ ALSO
MySQL Error seems unfixable: Index column size too large

MySQL Error seems unfixable: Index column size too large

I'm getting: ERROR 1709 (HY000): Index column size too largeThe maximum column size is 767 bytes

35
How do I pass my url params to async functions in javascript files separate from the route with Node.js?

How do I pass my url params to async functions in javascript files separate from the route with Node.js?

I'm just learning to code and I'm building an application for my businessI'm having an issue with passing url parameters on my show route

35
MySql combine records from same table

MySql combine records from same table

I'm attempting to preform a sql command to update rows, and combine rows with matching data

19
How to query between date and specific time range

How to query between date and specific time range

I need help on a query between date and specific time rangeFor example, a user entering the building from 1 Jan 2019 until 1 April 2019 and only from 7 PM until 10 PM for that date

30