https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43208
--- Comment #3 from Tomás Cohen Arazi (tcohen) <[email protected]> --- This is not easy to reproduce. Because many conditions affect how traversing the table (instead of the index) affects performance. What I think we could do is generate rows, and use MySQL's `EXPLAIN` statement to understand the effect of the change. Run this to generate rows: ```sql INSERT INTO action_logs (timestamp, user, module, action, object, info, interface) SELECT DATE_ADD('2025-01-01', INTERVAL FLOOR(RAND() * 500) DAY) as timestamp, 1, 'CATALOGUING', 'MODIFY', FLOOR(RAND() * 100000), 'test entry', 'intranet' FROM information_schema.columns a, information_schema.columns b LIMIT 50000; SELECT COUNT(*) as total_rows FROM action_logs; ``` Then you can use EXPLAIN to check if the queries use the index: ```sql EXPLAIN SELECT * FROM action_logs WHERE timestamp >= '2025-03-01T00:00:00Z' AND timestamp <= '2025-03-05T23:59:59Z'\G EXPLAIN SELECT * FROM action_logs WHERE timestamp >= '2025-03-01 00:00:00' AND timestamp <= '2025-03-05 23:59:59'\G ``` **ISO 8601 format (T separator)**: ``` id: 1 select_type: SIMPLE table: action_logs type: ALL possible_keys: timestamp_idx key: NULL key_len: NULL ref: NULL rows: 49938 Extra: Using where ``` **MySQL format (space separator)**: ```sql id: 1 select_type: SIMPLE table: action_logs type: range possible_keys: timestamp_idx key: timestamp_idx key_len: 4 ref: NULL rows: 520 Extra: Using index condition ``` And for time difference magnitude: ```sql SET profiling = 1; SELECT COUNT(*) FROM action_logs WHERE timestamp >= '2025-03-01T00:00:00Z' AND timestamp <= '2025-03-05T23:59:59Z'; SELECT COUNT(*) FROM action_logs WHERE timestamp >= '2025-03-01 00:00:00' AND timestamp <= '2025-03-05 23:59:59'; SHOW PROFILES; SET profiling = 0; ``` The `Duration` column is the key, on my local KTD I got this times: 5.35ms vs 0.13ms. 41× slower with the T separator format. -- You are receiving this mail because: You are watching all bug changes. _______________________________________________ Koha-bugs mailing list -- [email protected] To unsubscribe send an email to [email protected] website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
