Re: optimising a create table as select
Been on holiday so sorry for the delay and thanks for getting back to me.
I agree >> Joining FROM t1 > t2 WHERE t1.id = t2.trip_id is going to be the killer just to get a date <<
Both t1 trips and T2 readings could be combined into a single entity class as both table account for 99.7 of the data in the whole DB, alternatively I could denormalise the dates and partition them in trips which will make the ongoing maintenance much easier as you have pointed out.
The archive process will remove 80% of the data on T2 which accounts for 99.3% of the data. T2 is the database if you get my meaning !!
T1 trips has a 1 to Many with T2 readings
As requested the output is
SHOW CREATE TABLE(S) XXX
CREATE TABLE `historical_trips` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`status` int(11) DEFAULT '0',
`device_id` int(11) DEFAULT NULL,
`start_at` int(11) DEFAULT '0',
`end_at` int(11) DEFAULT '0',
`duration` int(11) DEFAULT '0',
`gps_miles` float DEFAULT '0',
`deceleration_1` int(11) DEFAULT '0',
`acceleration_1` int(11) DEFAULT '0',
`speeding_1` int(11) DEFAULT '0',
`morning` int(11) DEFAULT '0',
`evening` int(11) DEFAULT '0',
`night` int(11) DEFAULT '0',
`day` int(11) DEFAULT '0',
`num_engine_speed` int(11) DEFAULT '0',
`num_fuel` int(11) DEFAULT '0',
`ending_mileage` int(11) DEFAULT '0',
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
`start_date` date DEFAULT NULL,
`rush_hour_am` int(11) DEFAULT '0',
`highest_speed` float DEFAULT '0',
`idle` int(11) DEFAULT '0',
`cornering_1` int(11) DEFAULT '0',
`late_night` int(11) DEFAULT '0',
`time_zone` varchar(255) DEFAULT NULL,
`end_date` date DEFAULT NULL,
`rush_hour_pm` int(11) DEFAULT '0',
`qos_flags` int(11) DEFAULT '0',
`odometer_miles` float DEFAULT NULL,
`speeding_2` int(11) DEFAULT '0',
`deceleration_2` int(11) DEFAULT '0',
`name_tag_id` int(11) DEFAULT NULL,
`dest_tag_id` int(11) DEFAULT NULL,
`route_id` int(11) DEFAULT NULL,
`acceleration_2` int(11) DEFAULT '0',
`acceleration_3` int(11) DEFAULT '0',
`deceleration_3` int(11) DEFAULT '0',
`speeding_3` int(11) DEFAULT '0',
`cornering_2` int(11) DEFAULT '0',
`cornering_3` int(11) DEFAULT '0',
`policy_id` int(11) DEFAULT NULL,
`device_history_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `by_device_end_at` (`device_id`,`end_at`),
KEY `index_historical_trips_on_policy_id` (`policy_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16270514 DEFAULT CHARSET=latin1
CREATE TABLE `historical_readings` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`trip_id` int(11) DEFAULT NULL,
`latitude` decimal(15,10) DEFAULT NULL,
`longitude` decimal(15,10) DEFAULT NULL,
`altitude` float DEFAULT NULL,
`gps_speed` float DEFAULT NULL,
`heading` float DEFAULT NULL,
`satellites` int(11) DEFAULT NULL,
`event_code` varchar(255) DEFAULT NULL,
`update_time` int(11) DEFAULT NULL,
`sequence` int(11) DEFAULT NULL,
`geofence_event_type` varchar(255) DEFAULT NULL,
`rssi` float DEFAULT NULL,
`comm_state` varchar(255) DEFAULT NULL,
`hdop` float DEFAULT NULL,
`unit_status` varchar(255) DEFAULT NULL,
`engine_speed` float DEFAULT NULL,
`coolant_temp` float DEFAULT NULL,
`obd_trip_odometer` float DEFAULT NULL,
`throttle_position` float DEFAULT NULL,
`obd_speed` float DEFAULT NULL,
`odometer` float DEFAULT NULL,
`battery_voltage` float DEFAULT NULL,
`trip_fuel_consumption` float DEFAULT NULL,
`fuel_level` float DEFAULT NULL,
`qos_flags` int(11) DEFAULT '0',
`event_value` float DEFAULT NULL,
`micro_seconds` int(11) DEFAULT '0',
`fix_status` smallint(6) DEFAULT NULL,
`accel_samples` blob,
PRIMARY KEY (`id`),
KEY `index_historical_readings_on_trip_id` (`trip_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9984060919 DEFAULT CHARSET=latin1
Show vraible like Buffer
bulk_insert_buffer_size 8388608
innodb_buffer_pool_dump_at_shutdown OFF
innodb_buffer_pool_dump_now OFF
innodb_buffer_pool_filename ib_buffer_pool
innodb_buffer_pool_instances 8
innodb_buffer_pool_load_abort OFF
innodb_buffer_pool_load_at_startup OFF
innodb_buffer_pool_load_now OFF
innodb_buffer_pool_size 5704253440
innodb_change_buffer_max_size 25
innodb_change_buffering all
innodb_log_buffer_size 8388608
innodb_sort_buffer_size 1048576
join_buffer_size 262144
key_buffer_size 16777216
myisam_sort_buffer_size 8388608
net_buffer_length 16384
preload_buffer_size 32768
read_buffer_size 262144
read_rnd_buffer_size 524288
sort_buffer_size 262144
sql_buffer_result OFF