I'm not very experienced in database design so bear with me.
I've taken over a ruby/rails app. with a connected MySQL/innodb database. As I'm updating the server to the latest ruby/rails version I also wanted to look at the DB structure. My main concern is a specific database design decision which makes it very hacky to use with rails and I wanted to get some external input on this.
We are tracking spatial data for various systems from customers every x minutes which results in a lot of data. We don't have that data in one big "positions" table but split into a table for each system and each year. So e.g. for a system with id=1 and in the current year we have a "positions_1_2015" table.
So for, like, 1,000 systems over 3 years we already have 3,000 tables which seems a lot to me. When I want to change a column I have to change each single table instead of just one. I also have to do some hacking for this mechanism to work with rails activerecord logic.
Right now, summed up, we have about 300,000,000 positions from various customers. They are write once/read often and by multiple concurrent users.
So my question: is this table splitting a sensible design?
The argument from my clients is that they can more easily backup/archive all data and a lot of small tables are more manageable than one single huge table. But above all each client has access to their specific tables so that performance should be better when 100+ customers want to access their spatial data at the same time.
So is this true? Will performance be worse if Ihave 100+ concurrent users on a single big 300M row table?
Thanks for your help!
Edit: SHOW CREATE for one position table
CREATE TABLE `positions_1000_2015` ( `id` bigint(11) NOT NULL AUTO_INCREMENT, `world_x` double DEFAULT NULL, `world_y` double DEFAULT NULL, `timestamp` datetime DEFAULT NULL, `created_at` datetime NOT NULL, `status` int(11) DEFAULT NULL, `coworker_id` int(11) DEFAULT NULL, `raw_data` varchar(255) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL, `user_data` varchar(1024) DEFAULT NULL, `map_text` varchar(512) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL, `drive_number` int(11) DEFAULT NULL, `course` int(11) DEFAULT NULL, `speed` int(11) DEFAULT NULL, `max_speed` int(11) DEFAULT NULL, `distance` double DEFAULT NULL, `gsm_quality` int(11) DEFAULT NULL, `sat_count` int(11) DEFAULT NULL, `private` int(11) DEFAULT NULL, `user_created` tinyint(1) NOT NULL DEFAULT '0', `io_01` int(11) DEFAULT NULL, `io_02` int(11) DEFAULT NULL, `io_03` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `timestamp` (`timestamp`), KEY `world_x` (`world_x`), KEY `world_y` (`world_y`), KEY `status` (`status`), KEY `timestamp_status` (`timestamp`,`status`), KEY `status_timestamp` (`status`,`timestamp`), KEY `idx_raw_data` (`raw_data`), KEY `drive_number` (`drive_number`), KEY `created_at` (`created_at`) ) ENGINE=InnoDB AUTO_INCREMENT=1412811 DEFAULT CHARSET=utf8