CODEWITHSUNDEEP
mysqlsqlcountquery-optimizationdatabase-performance
Amr Abdalrahman Ahmed
Amr Abdalrahman Ahmed

Reputation: 1010

Mysql count performance on very big rows between two dates conditions

I have a table with more than 20 million rows in Innodb.

the columns are

id, viewable_id, visitor, viewed_at

where the viewable_id and viewed_at are indexes. when I do the below query

SELECT COUNT(*) 
FROM views_users 
WHERE (viewable_id = 2) 
  and (viewed_at between '2021-04-19 01:38:37' 
  and '2021-06-30 01:38:37');

=> take (3 min 6.72 sec)

the explain is

| id | select_type | table       | partitions | type | possible_keys                                             | key                           | key_len | ref   | rows    | filtered | Extra       |
+----+-------------+-------------+------------+------+-----------------------------------------------------------+-------------------------------+---------+-------+---------+----------+-------------+
|  1 | SIMPLE      | views_users | NULL       | ref  | views_users_viewable_id_index,views_users_viewed_at_index | views_users_viewable_id_index | 8       | const | 9554594 |    50.00 | Using where

How can I increase the performance to reach less than 4 seconds?

CREATE TABLE views_users (
    id int unsigned NOT NULL AUTO_INCREMENT,
    viewable_type varchar(255) NOT NULL,
    viewable_id bigint unsigned NOT NULL,
    visitor text,
    collection varchar(255) DEFAULT NULL,
    viewed_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY user_id (viewable_id)
) ENGINE=InnoDB AUTO_INCREMENT=20995848
    DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Upvotes: 2

Views: 841

Answers (2)

Amr Abdalrahman Ahmed
Amr Abdalrahman Ahmed

Reputation: 1010

I increase the performance to take less than 2 seconds by applying MySQL partiotons.

I used partition by range using the viewed_at column. change viewed_at type from timestamp to datatime and made it as primary key with id. make a cronjob runs on first day of each month that reorganize last partition into another partitions and so on.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270593

For this query:

SELECT COUNT(*)
FROM views_users
WHERE viewable_id = 2 and
      viewed_at between '2021-04-19 01:38:37' and '2021-06-30 01:38:37';

You can create an index:

CREATE INDEX idx_views_users_viewable_id_viewed_at ON views_users(viewable_id, viewed_at);

Upvotes: 0

Related Questions