i have one table MySQL InnoDB with 130000 rows and the next query is very slow.
SELECT COUNT(id) FROM mytable;
OR
SELECT COUNT(*) FROM mytable;
Query_time: 12.020727
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| id_user | int(11) | NO | | NULL | |
| id_method | int(11) | NO | | NULL | |
| date | datetime | NO | | NULL | |
| type | varchar(255) | YES | | NULL | |
| type_detail | varchar(255) | YES | | NULL | |
|id_manual_type| int(11) | YES | | NULL | |
| obs | longtext | YES | | NULL | |
| active | tinyint(1) | NO | | 1 | |
| created_by | int(11) | YES | | NULL | |
| created_at | datetime | YES | | NULL | |
| update_by | int(11) | YES | | NULL | |
| update_at | datetime | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
EXPLAIN SELECT COUNT(id) FROM mytable;
+--------------+--------------+-------------------------+---------+---------+--------------+----------------+---------+-----+-------+----------+-----------+
| id | select_type | table | partions| type | possible_keys| key | key_len | ref | rows | filtered | extra |
+--------------+--------------+-------------------------+---------+---------+--------------+----------------+---------+-----+-------+----------+-----------+
| 1 SIMPLE | mytable | NULL | index | NULL | id_manual_type | 4 | NULL| 130000| 100.00 |Using index|
+--------------+--------------+-------------------------+---------+---------+--------------+----------------+---------+-----+-------+----------+-----------+
What sort of optimizations on the database do I need to do?
- MYSQL 8.0.18
- Windows Server 2016
- 8GB RAM
- memory usage: 80%
- cpu usage: 30%
