CODEWITHSUNDEEP
mysqlindexingpush-notificationfirebase-cloud-messagingapple-push-notifications
anomaly92
anomaly92

Reputation: 37

How do I store push device tokens for APNS/FCM in a MySQL database with an index?

I would like to store the APNS device token and FCM registrationID in a MySQL database. I would like an index on the column that contains the token because I will be performing lookups using it and I expect the table to be considerably large (millions of rows).

As I understand it, there are no real limitations on the size of the device token that APNS or FCM uses. The documentation for APNS explicitly states to not make assumptions regarding the size (although it appears to be 64 characters). Due to this, I was planning on storing it as a longtext field in a MySQL database. However, MySQL prevents the creation of indexes that are greater than 767 characters.

Here are the options I am considering:

  1. Use the current format of APNS/FCM tokens to set a max length on the field where I store the tokens so that I can use an index
  2. Use a prefix index on a longtext field (not sure how inefficient this would be?)

Which of these options seems like the better way to go about this? Can you think of any alternative options?

Upvotes: 1

Views: 1199

Answers (1)

O. Jones
O. Jones

Reputation: 108776

Please don't use a CLOB (xxxTEXT) data type for this. They're grossly inefficient to store and index. This is a device token, so even if its length is not specified, it won't be dozens of kilobytes (MEDIUMTEXT) let alone many megabytes (LONGTEXT).

It looks like these are presently 64 characters of hex digits (0123456789abcdef). So, to be future proof you can use

 token VARCHAR(256) COLLATE latin1_bin NOT NULL

as the column definition. 256, four times the present size, should be enough for a while (that's a lot of mobile devices; the IPV6 address space will fill up first). And, the latin1 character set and bin collation will make for efficient index lookups.

Upvotes: 2

Related Questions