[SOLVED] MySql Slow Request Time

Issue

I have a very good pc and I was wondering why does it takes so long to make a very simple request to one of my table.

My pc:

• i9 10900kf (10 cores)
• 64 GB ram
• 2TB NVME SSD
• RTX 3090 Ventus

1: My table has 531 732 rows (this is not a lot of rows) with 39 columns

I have the following indexes to my table:

• DisplayName
• TmiSentTs
• Username
• Channel
• DisplayName_TmiSentTs
• Username_TmiSentTs

When I make the following query, it takes 66.016 seconds to get a response:

SELECT from_unixtime(TmiSentTs/1000,'%Y/%m/%d  %H:%i:%s'),
  DisplayName, message 
FROM MY_TABLE 
WHERE displayname LIKE '%ab%'
ORDER BY TmiSentTs DESC;

I don’t think this is normal.

I tried to:

1. change my innodb_write_io_threads from 4 to 32
2. change my innodb_read_io_threads from 4 to 32

But none of this works and I have another table (in another database) with 37 325 332 rows and it takes 2 seconds for a similar query.

EDIT:
After a bit of research, I found that this

SELECT * FROM pepegaclapwr.twitchmessages where instr('aa',username)<>0;

Is faster than

SELECT * FROM pepegaclapwr.twitchmessages where username like '%aa%';

For the same result

Solution

An index on the displayname column won’t help this query. Any LIKE condition with wildcards at the start of the pattern you are searching for cannot use an index, so it is bound to do a table-scan.

Think about a telephone book. If I ask you to look up last names that start with "T" it’s easy because the book is sorted by last name. But if I ask you to look up names where "T" is the 4th letter (or anything after the first letter), the fact that the book is sorted doesn’t help. You still have to read the whole book page by page to find the names I asked for.

To optimize the kind of query like the one you show above, you may find it easier to use a fulltext index, but that’s only if you are searching for whole words. It looks like you are searching for any string that contains "ab" somewhere in it. This is not a whole word, so a fulltext index won’t help either.

In that case, your only solution is to add another column to the table to indicate whether the row contains "ab", and then index that column. In MySQL 5.7 and later, you can can make a virtual column based on an expression.

ALTER TABLE MyTable
  ADD COLUMN displayname_ab TINYINT NOT NULL AS (displayname LIKE '%ab%'),
  ADD INDEX (displayname_ab);

Then search:

SELECT ... FROM MyTable WHERE displayname_ab = true;

In MySQL 8.0 you don’t even need to make a virtual column, you can make an expression index directly from an expression on an existing column:

ALTER TABLE MyTable
  ADD INDEX ((displayname LIKE '%ab%'));

Then if you search using the exact same expression, and it will use the index:

SELECT ... FROM MyTable WHERE displayname LIKE '%ab%';

But this fixes the string you need into the virtual column definition. It doesn’t help if you need to search for "cd" tomorrow, or any other pattern.

Answered By – Bill Karwin

Answer Checked By – Mary Flores (BugsFixing Volunteer)