CODEWITHSUNDEEP
mysqlsqlperformancequery-optimization
chifliiiii
chifliiiii

Reputation: 2359

How to optimize Mysql Select with count on 3GB table

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?

Upvotes: 2

Views: 257

Answers (2)

Willem Renzema
Willem Renzema

Reputation: 5187

Gordon really nailed it with the query, so my answer assumes you will be using his query.

A small improvement you can try is to change the date column data type to DATE, instead of DATETIME (Assuming you don't actually need the time portion.)

By doing that, you can reduce the storage on that column from 5 bytes (or 8, if using a version of MySQL older than 5.6) to 3, which will allow more data to be loaded at once, and so reduce some of the overhead.

Of course, if you need the time portion for other things, then this is not an option.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270993

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).

Upvotes: 2

Related Questions