What data type and index to use for a searchable userName column in a MySQL database?

Question

I have a MySQL database that has a InnoDB table called user.

Each user is required to create a user name during the creation of the account. The user name can only be between 1 and 40 characters (utf-8) long. The purpose of this user name is to allow for one user to search for another.

The problem is that I am not sure what datatype to use for the user name. Since the user name will be variable length a Varchar seems fitting but I am uncertain on how fast it is to index and do searches on a Varchar. The thought I had is that because I know the maximum character length I could use a Char(40) instead. Although this would waste some space due to the padding I would imagine it would make indexing and searching faster and more efficient since it is fixed length.

The other aspect I am uncertain about is if I should use a Fulltext index or a BTree index for this user name column. Or if there is an other index method I don't know about that would work better.

So which datatype in correlation with which index would give me the best, fastest and most scalable solution to this ?

Answer 1

Char vs Varchar

Char is useful when you have a fixed length, like with product reference or ISO-* field. Why ? because it'll spare you the use of a length byte on every line.

But in your case, the name will mostly never be over 20char, so to spare a single byte you'll burn more than 20 in 75% of your records: not the good way.

Varchar, definitely.

---

Index BTree vs FullText

Next, the index. As said, it depend of what kind of search you'll do.

For search that use ='pattern' or LIKE 'pattern%' the BTree index is really the best.

For search that only do LIKE '%pattern%' to search a partial sub string in a string, then, you don't really have anything.

The best you can do is having a "small" table that just map id = name, make your search on it and hope mysql will keep it in RAM to do the sequential read.

And last, you spoke about Fulltext index: this index is best suited for matching a word or begging of word in a text, so probably the same result than a BTree for you.

So, what the best way ?

Probably a BTree index. Yes you'll not cover the search inside the word, but for most case it'll be enough.

How to use it the best way ?

• Run a first search with LIKE 'pattern%'
• You have a result ? then this is probably what the user want
• No ? Go for a much slower LIKE '%pattern%'

Improvement :

• allowing the user to check an "inside word" modifier that go straight to the second search
• fixing a minimal number of line for the first query to avoid the 2nd and not just a strict 0 result