Performance issues on large CSV import with before INSERT trigger
My suggestions are roughly in order of the performance benefit they will provide.

(1) (Similar to what a_vlad says) Rethink the use of a Trigger.  I would load the csv into a temp table, then do the timezone lookup in a single query.  Only then copy the data into the main table.  Other tips on High speed ingestion .  Tips on chunking .  Yes, 500K rows at once is likely to cause some kind of trouble.

(2) Add to timezones:

INDEX(area_code, timezone)


(3) This is inefficient:

WHERE area_code = (SELECT LEFT(NEW.contact , 3));


change it to

WHERE area_code = LEFT(NEW.contact , 3);


(4) The first of these is redundant and can be dropped:

 KEY `FK_campaign_id_idx` (`campaign_id`),
 KEY `idx_004` (`campaign_id`,`call_status`,`test_call`),


(5) What is the distinction between contact and contact_name?

(6) Low-cardinality indexes (eg, idx_active_call) are essentially never chosen by the Optimizer, so could be dropped.