MySQL Forums
Forum List  »  Newbie

Re: optimising a create table as select
Posted by: Daniel Fisher
Date: May 15, 2014 05:02AM

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

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.