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

full-text-searchMySQLmysql-5.6

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?

Best Answer

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 = '...'.