Proper use of FULLTEXT index for a column storing unique hash value

Question

We have an InnoDB table called user, where we have a column called token (varchar(128)) where a unique md5-like value that represents whether a user is authenticated or not, is stored. This column can be filled or empty/NULL, depending on whether the user has authenticated yet or not.

Whenever an API is being called to make a change to other data, we always check whether the token sent with the API is valid or not. The query would be something like:

select * 
from `user` 
where token ='96e1e3c72ce665bc7aeb93a357801fa3296e500b1aed8016e5342e744990cd0094c98857ae5f4b0a73cc6dfebd11a8491a5ba8448e6ddff8a631eb8213c254d0'

As you can imagine, this query runs into seconds, because it is a query on a varchar field. Now I'm trying to fix the query speed.

I tried adding a FULLTEXT on the token field, but it won't help in the = query. So I'm trying to use MATCH() AGAINST() query as follows:

select * 
from `user` 
where MATCH(token) AGAINST('96e1e3c72ce665bc7aeb93a357801fa3296e500b1aed8016e5342e744990cd0094c98857ae5f4b0a73cc6dfebd11a8491a5ba8448e6ddff8a631eb8213c254d0')

But this query does not return any result. When I check use EXPLAIN, I get:

id 1
select_type SIMPLE
table user
type fulltext
possible_keys token
key token
key_len 0
ref NULL
rows 1
Extra Using where

Here, the key_len being 0, and rows saying 1, even though no results were returned, are suspicious to me.

Now I'm thinking even FULLTEXT isn't the solution - it doesn't seem to be working for completely unique values with no common words - not usual text.

Can you help me understand:

1. Why FULLTEXT index with MATCH() AGAINST() is not bringing any results?
2. What is the best way to index the token field and reduce query time?

Answer 1

No, I "can't imagine". That is, unless you did not have INDEX(token), which is the index that would fast. Don't use FULLTEXT in this situation.

To discuss further, please provide SHOW CREATE TABLE and EXPLAIN SELECT for the case of token = '...'.

• Aswin Kumar: Hey Rick, simply adding the index(token) did the trick, it looks like - I'll report back later if it hasn't. I'm not great at DBA, and I seem to have missed the obvious here - my impression was that index is for integer fields, and FULLTEXT is the only way to index varchar fields. Thank you kind sir for the guidance!
• Rick James: INDEXing a string column is useful when testing for = (in this case) or BETWEEN or certain other things. FULLTEXT is when you need to search for words within lengthy text.