As I've mentioned in previous questions, I am writing MySQL statements to update overlapping database entries and insert new data into various tables. My database design looks something like this, where there's one parent table (hash is the primary key and id is indexed):+---------------+| hashes        |+---------------+| hash  | id    |+---------------+| hash1 | id1   || hash2 | id1   || hash3 | id1   || hash4 | id2   |+---------------+And many child tables (id is indexed, there are no primary or unique keys):+-------------------------+| other_table             |+-------------------------+| id  | group_id  | value |+-------------------------+| id1 | groupid1  | val1  || id1 | groupid1  | val2  || id2 | groupid2  | val3  || id2 | groupid3  | val4  |+-------------------------+There are foreign key constraints on the child table id columns, so that if any ids in the hashes table are updated, all child tables update with the new information. The ids change when any inserted hashes are overlapped somewhere in the hashes table. To achieve all this, I wrote the following statement:START TRANSACTION;     /* Generated with PHP */                SET @id = '610de097-26d0-41b2-839b-1bd8c0d05dea';    SET @group_id = '54c41b95-5897-4984-961c-cc8fc97fc586';    /* Insert new data */    INSERT INTO hashes         (id, hash)     VALUES         (@id, 'hash1'), (@id, 'hash2')    ON DUPLICATE KEY UPDATE         repeat_count = repeat_count + 1;    INSERT IGNORE INTO categories         (id, group_id, value)    VALUES         (@id, @group_id, 'some value');COMMIT;And a collection of updates to run after everything is inserted:/* Update all parent table ids for any overlapping hashes */UPDATE hashes SET id=@idWHERE id IN (     SELECT id     FROM (SELECT id, hash FROM hashes ORDER BY id) as temp    WHERE hash IN ('hash1', 'hash2')    ORDER BY id ASC);This works, and with around 950 entries to insert, it takes around 25 seconds to run. The slowest part, by far, is the many update statements since it needs to search over the entire hash table and update rows accordingly.Consequently, if I want to see any speed improvements, I need to modify those update statements. Any ideas?
MySQL statements to update overlapping database entries and insert new data into various tables
performance;sql;mysql