1

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?
2

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

  • 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! – Aswin Kumar Nov 10 '18 at 13:40
  • 1
    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. – Rick James Nov 10 '18 at 19:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.