Setting charset to utf8mb4 makes query very slow

Question

I've been debugging this for hours and would greatly appreciate any help to see if I'm missing something very obvious. I have a MySQL table with 30k rows. The schema is as follows:

CREATE TABLE `log` (
  `ip` varchar(15) COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `date` int(11) NOT NULL,
  `requested` mediumtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `response` mediumtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `id` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

Indexes are:

ALTER TABLE `logs`
  ADD PRIMARY KEY (`id`) USING BTREE,
  ADD KEY `date` (`date`);

In phpmyadmin, when I query for SELECT ip, date, requested FROM log ORDER BY date DESC LIMIT 0, 1000, the query takes a few ms. However, in my PHP code, when I do the same query:

$mysqli = new mysqli(...);

$s = microtime(true);
$mysqli->set_charset('utf8mb4');
$query = $mysqli->prepare('SELECT ip, date, requested FROM log ORDER BY date DESC LIMIT 0, 1000');
$query->execute();
$query->bind_result($ip, $date, $requested);
while($query->fetch()) {
  // ... just echoing the results
}

$e = microtime(true);
echo $e - $s;

It takes 6-7 seconds. The column id is the primary key, and indexes are set to PRIMARY and date.

Some observations and debugging steps:

If I remove the set_charset, it takes 1.5 seconds.
If I keep the utf8mb4 charset but only select ip and date, the query finishes in ~0.002 seconds.
I searched online for solutions, and e.g. this solution did not make any difference.
EXPLAIN indicates that possible_keys is null, which is odd because it seems like date should be used as the index. Even FORCE INDEX does not make it use the index.
This was working fine before I changed the collation to utf8mb4_unicode_520_ci -- it was utf8_unicode before.

What am I missing? How can I speed up this query to something reasonable? Thank you so much in advance!! :)

And you should not store IP address as VARCHAR(15). Google `INET_ATON` and `INET_NTOA` — user1597430, May 24 '20 at 02:28
... and `date` should to not be INT(11). If you want to store x32 timestamp - make it UNSIGNED at least. — user1597430, May 24 '20 at 02:29
@user1597430 I'm confused because phpmyadmin does say it has indexes, it says it's using `id` and `date` as indexes. @nbk no good reason, except that it's an old table and legacy code. Thanks! — Michael, May 24 '20 at 02:38
Also, you should avoid pagination without indexes. I'm talking about the "offset" section. — The Impaler, May 24 '20 at 02:39
@Michael, I believe, you are looking in the wrong place. Indexes in phpMyAdmin can be verified via visual inspection of "Structure" tab. — user1597430, May 24 '20 at 02:45
Btw, you can narrow down the `prepare/execute` to simply using `query()`. What you have there isn't needed since there's no user input. It won't add much to performance but... every little bit counts `;-)` — Funk Forty Niner, May 24 '20 at 02:55
@user1597430 In the "Structure" tab, I see "Indexes" section, and in there I see the two indexes I mentioned above (id and date). Is this the wrong place? I created the index by pressing the dropdown next to the column and pressing "Index", which does `ALTER TABLE `logs` ADD INDEX(`date`);` I know this question is closed now but is this the correct way to add indexes? Thank you! — Michael, May 24 '20 at 03:02
@user1597430 I read through the linked answer but still cannot figure out why my query is taking so long if the index is correctly set. Any additional help would be greatly appreciated :) — Michael, May 24 '20 at 03:29
@Michael, I haven't closed your question, so I can't comment the linked answer or anything else. However, I can recommend you a few things. First of all, your method of timing is wrong. You should check the performance without "echo" calls. Secondly, you should run something like mysqltuner to confirm that your MySQL config is not wrong: for some reasons you could allocate less RAM for indexes than you need (key_buffer_size) or your caching system for MyISAM engine is completely disabled (it happens when you have InnoDB + MyISAM on the same server). — user1597430, May 24 '20 at 04:45
@FunkFortyNiner - I don't see why https://stackoverflow.com/questions/35627639/mysql-query-performs-very-slow is relevant. Voting to reopen. — Rick James, May 24 '20 at 20:19

Rick James · Answer 1 · 2020-05-24T20:32:47.773

SELECT ip, date, requested FROM log
    ORDER BY date DESC LIMIT 0, 1000

needs INDEX(date).

If you want the latest 20 from one ip,

SELECT ip, date, requested FROM log
    WHERE ip = '11.22.33.44'
    ORDER BY date DESC LIMIT 0, 20

needs INDEX(IP, date).

If ip is an IP address, then ascii is sufficient. This will only slightly faster than utf8mb4. The character set is not the cause of the slowdown; the lack of an index is.

Another reason for a slowdown is shoveling 1000 bulky(?) rows.

A possible reason for the slowdown and the wide variations in timings is the value of innodb_buffer_pool_size If the buffer_pool is too small to hold the data; it will be hitting the disk. How much RAM do you have? The buffer_pool_size should be set to about 70% of available RAM.

Why is date an INT? See datatypes DATETIME, DATE, TIMESTAMP.

Please provide the EXPLAIN; some things you say don't agree with each other. And do EXPLAIN FORMAT=JSON SELECT ... for more info. (If that format is not available, then it time to upgrade your MySQL version.)

Are you ignoring the existence of IPv6?

Setting charset to utf8mb4 makes query very slow

1 Answers1