Krdytkyu

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=@id

WHERE 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?

I was going in circles looking at your query and then I thought
... it looks like you're just updating the one table form the same table

Why can't you just use

UPDATE hashes 

SET id=@id

    WHERE hash IN ('hash1', 'hash2');

Or is this a typo?

UPDATE hashes