In brief: I'm developing a database that handles GTFS datasets from multiple transit agencies. Each dataset contains millions of rows in the stop_times.txt file (and thus its corresponding table). Updating the table gets slower and slower as it gets bigger. I can deal with a couple of million rows from a single agency, but what happens when I add 10 more feeds? 50?
Now, the data sets are completely independent of one another. I won't be trying to join information across DART, MTA, and Transport for London. I feel like it would be very bad database design, but I'm tempted to create a separate table for each and forget about the whole thing.
I'm sure this has been answered somewhere, but I really don't know what I'm searching for. I've read up a bit on partitioning, but I'm not sure if that will solve my problem. Would adding a hash partition on my
agency_id field solve issues with exploding BTREE indexes?
Here's my current table structure:
CREATE TABLE `stop_times` ( `trip_id` bigint(20) unsigned DEFAULT NULL, `arrival_time` time DEFAULT NULL, `departure_time` time DEFAULT NULL, `stop_id` bigint(20) unsigned DEFAULT NULL, `stop_sequence` smallint(5) unsigned DEFAULT NULL, `stop_headsign` tinytext, `route_id` mediumint(8) unsigned DEFAULT NULL, `feed_id` smallint(5) unsigned DEFAULT NULL, `update_id` int(10) unsigned DEFAULT NULL, UNIQUE KEY `stop_sequence` (`trip_id`,`stop_sequence`) USING HASH, KEY `trip_id` (`trip_id`) USING HASH, KEY `departure_time` (`departure_time`) USING BTREE, KEY `stop_id` (`stop_id`) USING HASH, KEY `feed_id` (`feed_id`) USING HASH, KEY `update_id` (`update_id`) USING HASH ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Thanks in advance for the help.