How to optimize Mysql Select with count on 3GB table

Question

I have a table with 37,000,000 rows and 3gb of data.

CREATE TABLE `stats_raw` (
    `user_id` INT(11) NOT NULL,
    `date` DATETIME(6) NOT NULL,
    `ip` VARBINARY(16) NULL DEFAULT NULL,
    INDEX `stats_raw_user_id_index` (`user_id`),
    INDEX `stats_raw_date_index` (`date`),
    INDEX `stats_raw_user_id_data_index` (`user_id`, `date`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
;

When I try to perform the following query (generated by laravel, that's why it may look weird):

select count(ip) as total, INET6_NTOA(ip) as ip
from `stats_raw`
where `user_id` = 1 and date(`date`) >= '2019-02-10'
group by `ip`
order by `total` desc
limit 10

It takes around 40 seconds to return results.

How can I optimize this on mysql?

Answer 1

First, I would write the query as:

select count(ip) as total, INET6_NTOA(ip) as ip
from `stats_raw`
where `user_id` = 1 and `date` >= '2019-02-10'
group by `ip`
order by `total` desc
limit 10;

Second, you want an index on stats_raw(user_id, date, ip).

That said, it is not clear how much data is being processed. I don't think there is a way around the sorting for the group by and order by, so if you have lots of data, you may not be able to speed this query, without more heroic efforts (such as maintaining summary tables using triggers).