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:
- Why
FULLTEXT
index withMATCH() AGAINST()
is not bringing any results? - 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 useFULLTEXT
in this situation.To discuss further, please provide
SHOW CREATE TABLE
andEXPLAIN SELECT
for the case oftoken = '...'
.