the “force index” is needed. Without it, it takes index " index_idsite_config_datetime" over the new index.
So I decided to do another experiment.
- I dropped the new index ind_piwik_log_visit_01, explain the query without using the “force index”. the 2nd query is without column “idsite” in the WHERE clause. And MySQL gave it a “FULL TABLE SCAN”. it doesn’t even recognize the other indice “index_idsite_datetime or index_idsite_idvisitor” with column “visit_last_action_time”
mysql> explain SELECT COUNT(log_visit.idvisitor) AS `1`, COUNT(log_visit.user_id) AS `39`
FROM piwik_log_visit log_visit
WHERE visit_last_action_time >= '2019-01-0 1 00:00:00'
and visit_last_action_time <= '2019-02-00 00:00:00'
AND idsite IN ('1');
+----+-------------+-----------+------------+------+---------------------------------------------------------------------------+------------------------------+---------+-------+----------+---- ------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | fil tered | Extra |
+----+-------------+-----------+------------+------+---------------------------------------------------------------------------+------------------------------+---------+-------+----------+---- ------+-----------------------+
| 1 | SIMPLE | log_visit | NULL | ref | index_idsite_config_datetime,index_idsite_datetime,index_idsite_idvisitor | index_idsite_config_datetime | 4 | const | 42417112 | 11.11 | Using index condition |
+----+-------------+-----------+------------+------+---------------------------------------------------------------------------+------------------------------+---------+-------+----------+---- ------+-----------------------+
1 row in set, 2 warnings (0.37 sec)
mysql> mysql> explain SELECT COUNT(log_visit.idvisitor) AS `1`, COUNT(log_visit.user_id) AS `39`
FROM piwik_log_visit log_visit
WHERE visit_last_action_time >= '2019-01-01 00
and visind visit_last_action_time <= '2019-02-00 00:00:00' ;
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| 1 | SIMPLE | log_visit | NULL | ALL | NULL | NULL | NULL | NULL | 84834762 | 11.11 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
- I created the new index again and analyzed the table. Then re-ran the same 2 queries from above.
mysql> create index ind_piwik_log_visit_01 on piwik_log_visit(visit_last_action_time,idsite,idvisitor);
Query OK, 0 rows affected (7 min 25.46 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> analyze table piwik_log_visit;
+-----------------------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------------------------+---------+----------+----------+
| fdsys_mysql_piwik.piwik_log_visit | analyze | status | OK |
+-----------------------------------+---------+----------+----------+
1 row in set (3.32 sec)
mysql> explain SELECT COUNT(log_visit.idvisitor) AS `1`, COUNT(log_visit.user_id) AS `39`
FROM piwik_log_visit log_visit
WHERE visit_last_action_time >= '2019-01-0 1 00:00:00'
and visit_last_action_time <= '2019-02-00 00:00:00'
AND idsite IN ('1');
+----+-------------+-----------+------------+------+--------------------------------------------------------------------------------------------------+------------------------------+---------+-------+----------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+--------------------------------------------------------------------------------------------------+------------------------------+---------+-------+----------+----------+-----------------------+
| 1 | SIMPLE | log_visit | NULL | ref | index_idsite_config_datetime,index_idsite_datetime,index_idsite_idvisitor,ind_piwik_log_visit_01 | index_idsite_config_datetime | 4 | const | 38831407 | 50.00 | Using index condition |
+----+-------------+-----------+------------+------+--------------------------------------------------------------------------------------------------+------------------------------+---------+-------+----------+----------+-----------------------+
1 row in set, 6 warnings (0.81 sec)
mysql> explain SELECT COUNT(log_visit.idvisitor) AS `1`, COUNT(log_visit.user_id) AS `39`
FROM piwik_log_visit log_visit
WHERE visit_last_action_time >= '2019-01-0 1 00:00:00'
and visit_last_action_time <= '2019-02-00 00:00:00';
+----+-------------+-----------+------------+-------+------------------------+------------------------+---------+------+----------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+------------------------+------------------------+---------+------+----------+----------+--------------------------+
| 1 | SIMPLE | log_visit | NULL | range | ind_piwik_log_visit_01 | ind_piwik_log_visit_01 | 5 | NULL | 38831407 | 100.00 | Using where; Using index |
+----+-------------+-----------+------------+-------+------------------------+------------------------+---------+------+----------+----------+--------------------------+
1 row in set, 4 warnings (0.00 sec)
- I dropped and re-created the new index with different order of the columns
mysql> drop index ind_piwik_log_visit_01 on piwik_log_visit;
Query OK, 0 rows affected (0.57 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create index ind_piwik_log_visit_01 on piwik_log_visit(idsite,visit_last_action_time,idvisitor);
Query OK, 0 rows affected (33 min 52.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> analyze table piwik_log_visit; +-----------------------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------------------------+---------+----------+----------+
| fdsys_mysql_piwik.piwik_log_visit | analyze | status | OK |
+-----------------------------------+---------+----------+----------+
1 row in set (0.81 sec)
mysql> explain SELECT COUNT(log_visit.idvisitor) AS `1`, COUNT(log_visit.user_id) AS `39` FROM piwik_log_visit log_visit WHERE visit_last_action_time >= '2019-01-01 00:00:00' AND idsite IN ('1');
+----+-------------+-----------+------------+------+--------------------------------------------------------------------------------------------------+------------------------------+---------+-------+----------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+--------------------------------------------------------------------------------------------------+------------------------------+---------+-------+----------+----------+-----------------------+
| 1 | SIMPLE | log_visit | NULL | ref | index_idsite_config_datetime,index_idsite_datetime,index_idsite_idvisitor,ind_piwik_log_visit_01 | index_idsite_config_datetime | 4 | const | 38873071 | 33.33 | Using index condition |
+----+-------------+-----------+------------+------+--------------------------------------------------------------------------------------------------+------------------------------+---------+-------+----------+----------+-----------------------+
1 row in set, 1 warning (0.15 sec)
mysql> explain SELECT COUNT(log_visit.idvisitor) AS `1`, COUNT(log_visit.user_id) AS `39` FROM piwik_log_visit log_visit WHERE visit_last_action_time >= '2019-01-01 00:00:00' ;
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| 1 | SIMPLE | log_visit | NULL | ALL | NULL | NULL | NULL | NULL | 77746143 | 33.33 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
Based on my observation, MySQL database utilizes only the index starting with a column that appears in the WHERE clause. If there are multiple options, then it takes what’s available first based on the indices creation sequence. MySQL doesn’t recognize a index if the WHERE clause columns are not at the beginning of the column’s order. For example, The Index “index_idsite_config_datetime,index_idsite_datetime,index_idsite_idvisitor” are all started with column “idsite”.
In case 3, when I re-created index with different column order => ind_piwik_log_visit_01 “idsite,visit_last_action_time,idvisitor”, then 2nd explain at case3 has a full table scan. it doesn’t utilize ind_piwik_log_visit_01
Conclusion: it will be easier for Matomo to include the “force index” into the query. So the query will utilize the best fit index, instead of picking “first available” index.
@thomas_matomo I don’t know what’s your server specs. But when you said “5 seconds” of the query, I assumed you ran it without creating the new index. Also we don’t use “user id” since we don’t track “user id” at all. And all users are anonymous.
here is my specs.
# lscpu|grep -E '^Thread|^Core|^Socket|^CPU'
CPU op-mode(s): 32-bit, 64-bit
CPU(s): 8
Thread(s) per core: 1
Core(s) per socket: 1
Socket(s): 8
CPU family: 6
CPU MHz: 2394.231
# free -m
total used free shared buffers cached
Mem: 32109 29548 2560 0 131 6351
-/+ buffers/cache: 23065 9043
Swap: 1023 0 1023
/etc/my.cnf
innodb_buffer_pool_size = 20G
innodb_log_buffer_size = 512M
innodb_log_file_size = 1G
innodb_write_io_threads = 32
innodb_read_io_threads = 32
innodb_io_capacity = 5000
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = 1
max_allowed_packet = 256M
innodb_flush_method = O_DIRECT
innodb_doublewrite = 0
innodb_support_xa = 0
innodb_checksums = 0
wait_timeout = 30000
max_connections = 250
I’m still hoping you can re-consider including the new index and “force index” to the new release. Thank you~