Re: INSERTing when there is a foreign key table

[Rick James]

Do you need the file_ids to stay the same from week to week? If not, ...

1. LOAD DATA into accesses
2. CREATE TABLE file_names (id INT AUTO_INCREMENT ... PRIMARY KEY ...);
3. INSERT INTO file_names (file_name) SELECT DISTINCT file_name FROM access;
4. ALTER TABLE accesses ADD COLUMN file_id INT;
5. UPDATE ... (specify both tables, update only accesses)
6. ALTER TABLE accesses DROP COLUMN file_name;

Somewhere in there, you may need to add appropriate indexes.

Note: The ALTER TABLEs will temporarily need as much extra disk space as accesses.

If you want to do this on a live system, swap the new copy by first writing it to "new" instead of "accesses, then:
7. RENAME TABLE access TO old, new TO access;
8. DROP TABLE old;

That will be instantaneous.