Traewelling / traewelling

Free check-in service to log your public transit journeys
https://traewelling.de
GNU Affero General Public License v3.0
225 stars 44 forks source link

:card_file_box: add index for `operator_id` and `category` on `hafas_trips` #2583

Closed MrKrisKrisu closed 1 month ago

MrKrisKrisu commented 1 month ago
# Thread_id: 34524  Schema: traewelling  QC_hit: No
# Query_time: 2.286532  Lock_time: 0.000010  Rows_sent: 840  Rows_examined: 2080256
# Rows_affected: 0  Bytes_sent: 16384
# Tmp_tables: 1  Tmp_disk_tables: 0  Tmp_table_sizes: 933840
# Full_scan: Yes  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: No
# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: No
#
# explain: id   select_type table   type    possible_keys   key key_len ref rows    r_rows  filtered    r_filtered  Extra
# explain: 1    SIMPLE  hafas_trips ALL NULL    NULL    NULL    NULL    2078545 2078576.00  100.00  100.00  Using temporary; Using filesort
#
SET timestamp=1716383613;
select count(*) AS total, operator_id, category from `hafas_trips` group by `operator_id`, `category`;