Vfrdtyky

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.

EDIT: Here is the explain statement for the above query.