I am trying to optimise a MySQL script that I have created. Here is a bit of background on the project.
We have 2 system which we are now merging into one system, I am currently writing the scripts to merge the databases together. I need this to run as fast as possible, while this script is running we have to shut down the systems and we don't want too much down time.
Both systems have a table called venue, zone and device_vendor which are identical they just have different data. When I merged the tables together obviously the IDs for the values in those tables have changed and they are foreign keys in a lot of other tables. The table I am currently working on has 1.7 billion records and all has the values venue_id, zone_id and device_vendor_id so I have to change these values to the new IDs.
Here is the script that I have written to change the IDs to the new values. All these values are also indexes which is why it is taking so long.
INSERT INTO `intelli_sense`.`tracking_daily_stats_zone_unique_device_uuids_per_hour` (day_epoch, day_of_week, hour, venue_id, zone_id, device_uuid, device_vendor_id, first_seen, last_seen, is_repeat)
SELECT day_epoch, day_of_week, hour, (CASE WHEN intelli_sense_venue.id!=0 THEN intelli_sense_venue.id ELSE 0 END), (CASE WHEN intelli_sense_zone.id!=0 THEN intelli_sense_zone.id ELSE 0 END), device_uuid, (CASE WHEN intelli_sense_device_vendor.id!=0 THEN intelli_sense_device_vendor.id ELSE 0 END), first_seen, last_seen, is_repeat
FROM geo_sense.daily_stats_zone_unique_device_uuids_per_hour AS tracking_daily_stats_zone_unique_device_uuids_per_hour
LEFT JOIN geo_sense.venue AS tracking_venue ON tracking_venue.id = tracking_daily_stats_zone_unique_device_uuids_per_hour.venue_id
LEFT JOIN intelli_sense.venue AS intelli_sense_venue ON intelli_sense_venue.name = tracking_venue.name
LEFT JOIN geo_sense.zone AS tracking_zone ON tracking_zone.id = tracking_daily_stats_zone_unique_device_uuids_per_hour.zone_id
LEFT JOIN intelli_sense.zone AS intelli_sense_zone ON intelli_sense_zone.name = tracking_zone.name AND intelli_sense_zone.lat = tracking_zone.lat AND intelli_sense_zone.lon = tracking_zone.lon
LEFT JOIN geo_sense.device_vendor AS tracking_device_vendor ON tracking_device_vendor.id = tracking_daily_stats_zone_unique_device_uuids_per_hour.device_vendor_id
LEFT JOIN intelli_sense.device_vendor AS intelli_sense_device_vendor ON intelli_sense_device_vendor.name = tracking_device_vendor.name AND intelli_sense_device_vendor.description = tracking_device_vendor.description;
As you can see from the script above I have used LEFT JOINS to update these values. I have basically imported both databases into one server and now I am merging them together to create a new database called intelli_sense.
In the script above this isn't merging the 2 databases together it is just updating the values in the tracking_daily_stats_zone_unique_device_uuids_per_hour table which is from the geo_sense database because after merging the venue, zone and device_vendor tables the IDs have changed. I need to update all the tables which are related to those tables.
I have ran this script and it took 3 days to insert 50mil records which is way to long so I stopped it.
I need this script to finish in less then a day because I have 3 tables with this amount of data and I don't want too much system downtime.
I can't share the data since there is so much so I hope that script explains it enough, the actual script works its just a case of speeding it up. The data is also personal data so I wouldn't be able to share it anyway.
If you don't think this is impossible let me know and I will find a new way to do it while leaving both systems on so it can take as long as it wants.
Thanks in advance.
You may notice a lot of ALLs in the type but I believe this is due to joining on the name. I have to do that because I no longer know what the IDs are since this is the reason I am using the
LEFT JOINs to change them values.