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
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:
MATCH() AGAINST()is not bringing any results?
- What is the best way to index the
tokenfield and reduce query time?