Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Which DBMS are you using? I presume MySQL due to the appearance of ENGINE=InnoDB. –  Colin 't Hart Oct 12 '13 at 7:34
    
Correct. I've edited the question. –  Mikkel Oct 12 '13 at 17:16
1  
Do you know that InnoDB has only Btree indexes and your USING HASH are silently converted to Btrees? –  ypercube Oct 12 '13 at 19:48
    
I wasn't aware of that, no. That would explain the output of my SHOW INDEXES command. Should I switch to a different storage engine? Or is that not a performance concern? –  Mikkel Oct 13 '13 at 5:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.