How to save varchar field for query performance in mysql innodb-CodePudding

I made the following sample table.

CREATE TABLE `User` (
  `userId` INT(11) NOT NULL AUTO_INCREMENT,
  `userKey` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`userId`),
  UNIQUE KEY (`userKey`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

I want a quick query speed for the userKey field. In order to do that, what else should I consider besides the index?

For example, when saving the userKey, would using a value such as a DATE(sortable value) as prefix be more beneficial to the search speed?
Or, if I save the userKey as the hash value, will it improve?

CodePudding user response：

You've declared userKey as UNIQUE, so MySQL will automatically create an index for you. That should generally be enough, given that you are searching by "exact match" or by "starting with" type of queries.

If you consider storing hash values, you would only be able to perform "exact match" queries. However, for short strings that may turn out to be not really worth it. Also, with hashes, you need to consider the risk of a collision. If you use a smaller hash value (say 4 bytes) that risk increases. Those are just some guidelines. It's not possible to provide a definitive solution without delving into more details of your case.

CodePudding user response：

One way to speed the table is to get rid of it.

Building a mapping from an under 20 char string to a 4-byte INT does not save much space. Getting rid of the lookup saves a little time.

If you need the to do the 'right thing' had have one copy of each string to avoid redundancy, then what you have is probably the best possible.

If there are more columns, then there may a slight improvement. But first, answer this: Do you do the lookup by UserKey more of often than by UserId?

I give a resounding NO to a Hash. (Such might be necessary if the strings were too big to index.)

A BTree is very efficient as doing a "point query". (FROM User WHERE UserId = 123 or FROM User WHERE UserKey = 'xyz'). Adding a date or hash would only make it slower.