ovo je tabela:
+-----------+------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------------+------+-----+---------+----------------+
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| type | enum('profile','post') | NO | | profile | |
| tiktok_id | varchar(200) | NO | | | |
| is_new | tinyint(1) unsigned | NO | | 0 | |
| date | varchar(20) | NO | MUL | | |
+-----------+------------------------+------+-----+---------+----------------+
i index:
ALTER TABLE `whole_logs_data` ADD INDEX `numberOne` (`date`, `type`);
Tabela ima 93.9M redova.
Ovaj query odradi za 28s:
SELECT `date`, `type`, COUNT(*) AS `total` FROM `whole_logs_data` GROUP BY `date`, `type`;
i explain:
+------+-------------+-----------------+-------+---------------+-----------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------------+-------+---------------+-----------+---------+------+----------+-------------+
| 1 | SIMPLE | whole_logs_data | index | NULL | numberOne | 83 | NULL | 90208260 | Using index |
+------+-------------+-----------------+-------+---------------+-----------+---------+------+----------+-------------+
1 row in set (0.001 sec)
Ali ovaj vrti skoro 3 minuta
SELECT `date`, `type`, COUNT(*) AS `total`, SUM(`is_new`) AS `as_new` FROM `whole_logs_data` GROUP BY `date`, `type`;
i expain
+------+-------------+-----------------+-------+---------------+-----------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------------+-------+---------------+-----------+---------+------+----------+-------------+
| 1 | SIMPLE | whole_logs_data | index | NULL | numberOne | 83 | NULL | 90208260 | Using index |
+------+-------------+-----------------+-------+---------------+-----------+---------+------+----------+-------------+
Dakle meni treba da mi za svaki dan kaze koliko ima redova i koliko ima novih (za post i profile) zato i group by na kraju sa `type` a pre toga po danu i s toga i kompozitni index.
Kako je is_new tinynt i ima vrednosti 0 ili 1 onda mi je logicno bilo da odradim SUM nad ovom kolonom i tako dobijem koliko ima 'novih'.
Explain je isiti za oba upita kao sto vidite.
Kontam da ovaj SUM zeza, ali ne znam zasto ili nekako drugacije da se napravi upit.
Gde gresim?
Evo i data kako izgleda:
+----+---------+---------------------+--------+------------+
| id | type | tiktok_id | is_new | date |
+----+---------+---------------------+--------+------------+
| 1 | profile | 7077602043296498693 | 1 | 30-08-2022 |
| 2 | profile | 6937903706689127426 | 1 | 30-08-2022 |
| 3 | profile | 7081503130034160641 | 1 | 30-08-2022 |
| 4 | profile | 7126714382477755434 | 1 | 30-08-2022 |
| 5 | profile | 7060270017213236225 | 1 | 30-08-2022 |
| 6 | profile | 6811530645399962626 | 1 | 30-08-2022 |
| 7 | profile | 6823176361516696577 | 1 | 30-08-2022 |
| 8 | profile | 7061271021571032066 | 1 | 30-08-2022 |
| 9 | profile | 109011566246670336 | 1 | 30-08-2022 |
| 10 | profile | 6828957007870592005 | 1 | 30-08-2022 |
+----+---------+---------------------+--------+------------+