MySQL Sum Very Slow On Small Number of Rows

Question

I can't figure out why MySQL is so slow summing less than 400 rows. Both u and t have indexes and return the rows quickly.

SELECT sum(t) FROM `s_table` 
    WHERE `u` LIKE 'dogs%'
      AND `t`> 10000

Query took 3.5299

If I remove the sum part of the query.

SELECT t FROM `s_table` 
    WHERE `u` LIKE 'dogs%' 
      AND `t`> 10000

Query took 0.0090 seconds returns 397 rows.

So to sum 397 rows takes over 3 seconds!

Then I tried.

SELECT SUM(t)
    FROM ( SELECT t
               FROM s_table
               WHERE `u` LIKE 'dogs%'
                 AND `t`> 10000
         ) AS total;

Query took 3.5767 seconds, so basically the same as the first query.

I'm going insane here. Why is it taking MySQL over 3 seconds to sum only 398 numbers?

Here is the explain:

CREATE TABLE `s_table` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `s` varchar(100) NOT NULL,
 `v` int(12) NOT NULL,
 `c` float NOT NULL,
 `r` int(3) NOT NULL,
 `u` varchar(350) NOT NULL,
 `w` int(1) NOT NULL,
 `t` int(12) NOT NULL,
 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `idx_v` (`v`),
 KEY `idx_c` (`c`),
 KEY `idx_r` (`r`),
 KEY `idx_u` (`u`),
 KEY `idx_t` (`t`),
 KEY `idx_date` (`date`),
 KEY `s` (`s`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED

1) Did you have a look in the execution plan? 2) Can you check which condition makes the sum slow? Meaning removing the entire where clause or the first condition or the second? 3) Does the issue occur when using sum only or also using other functions like count etc? — Jonas Metzler, Apr 30 '22 at 14:41
Please post your result using EXPLAIN keyword at the beginning of your query — Asgar, Apr 30 '22 at 14:43
https://stackoverflow.com/questions/2042269/how-to-speed-up-select-like-queries-in-mysql-on-multiple-columns - If you need any clarification please refer to @reko_t answer. — Jakaria Ridoy, Apr 30 '22 at 14:45
I've added the explain above. The LIKE 'dogs%' returns very quickly (0.0090 seconds) if I remove the sum. I did try count() also and that is slow as well. — Dan, Apr 30 '22 at 14:53
The reason why I asked about the conditions in your where clause is that I assume the like to cause this bad performance. Could you please check this? Which time does the query need when writing = 'dogs' instead? — Jonas Metzler, Apr 30 '22 at 14:57
@Dan There are `272580` which matches your condition, when did you update statistics last time? — D-Shih, Apr 30 '22 at 14:58
Could you try to `ANALYZE TABLE s_table;` before execute your query? — D-Shih, Apr 30 '22 at 15:01
But if I remove the sum it return 397 records not 272580. That's all I want to sum. Those 397 records. I've added a screenshot above. — Dan, Apr 30 '22 at 15:03
Show the output for `SHOW CREATE TABLE s_table;`. Try to add an index by `(u, t)`. — Akina, Apr 30 '22 at 15:06
I had already added a (u,t) index but space usage was double having separate indexes on each of them which wasn't an option. It just makes no sense to me that MySQL finds the 397 rows instantly then resorts to some count of 272580 rows to do the summing. — Dan, Apr 30 '22 at 15:13
*I had already added a (u,t) index but space usage was double having separate indexes on each of them which wasn't an option.* The presence of `(u,t)` index makes the index by `(u)` excess. Remove it. Totally you will have 2 indices which occupies approximately the same disk space. And where is the output for SHOW CREATE TABLE? I do not see it... — Akina, Apr 30 '22 at 16:04
I've added the show create table. I should clarify, I need the u column indexed with every column. So if I do indexes of (u,t) and (u,v) and (u,s) etc.. the table is huge. All other queries are fast and work fine with separate indexes. For SUM and COUNT mysql is doing some crazy extra steps it doesn't need to for no reason that I can find. At the very least this query should work quickly and it doesn't SELECT SUM(t) FROM ( SELECT t FROM s_table WHERE `u` LIKE 'dogs%' AND `t`> 10000 ) AS total; — Dan, Apr 30 '22 at 16:31
How do SQL-based RDBMSs make queries like yours fast? With compound indexes. Do they take disk / SSD space. Yes they do. Can they make a vast difference to query performance? Yes. At any rate, you're comparing two queries, one doing an aggregate and the other retrieving a multirow result set. Have you compared the ANALYZE executiion plans (actual execution plans) of the two queries? If not, you need to. As for "why", what version of MariaDB / MySQL do you use? Query optimization is an ongoing effort for those developers. — O. Jones, Apr 30 '22 at 16:48
Does the condition by `u` column is always searching for a value which starts from definite 4 chars? — Akina, Apr 30 '22 at 20:59

Rick James · Answer 1 · 2022-05-01T16:53:40.550

Change these

KEY `idx_u` (`u`),
KEY `idx_t` (`t`),

to

KEY `u_t` (`u`, `t`),
KEY `t_u` (`t`, `u`),

I said "change", not "add". I have seen cases where "adding" a composite index did not change the Optimizer's choice of index; I think this is a bug. Note that these 2-column indexes are "covering", which, by itself, gives a performance boost.

Don't use ROW_FORMAT=COMPRESSED, it may be expending a lot of effort in uncompressing to run the query.

The UI you are using seems to stop at 25 rows -- this could explain the extra speed.

What do you get from these? (They may help in analyzing things.)

How many rows "need" to be looked at by each single-column index:
```
SELECT SUM(U LIKE 'dogs%'), SUM(t > 10000) FROM s_table;
```

More details than a plain EXPLAIN:

EXPLAIN FORMAT=JSON SELECT ...  -- your query

This will definitively say whether 397 versus 272580 rows were fetched:

FLUSH STATUS;
SELECT ...;  -- your query
SHOW SESSION STATUS LIKE 'Handler%';

MySQL Sum Very Slow On Small Number of Rows

1 Answers1