3
\$\begingroup\$

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?

\$\endgroup\$
  • \$\begingroup\$ I don't know if MySQL recognizes that your ORDER BY's are not needed, but you can see if it makes any noticeable difference removing them from your SELECTs. Maybe I'm missing something but I wouldn't think an UPDATE would need to be dependent on the data order \$\endgroup\$ – dbmitch Jul 7 '16 at 19:34
  • \$\begingroup\$ @dbmitch Just checked, difference is negligible. \$\endgroup\$ – Charlie Jul 7 '16 at 19:43
  • \$\begingroup\$ Hello! Please don't make changes to the original post once it has been reviewed, as that invalidates the current answers. Please see our meta side on performing iterative reviews for more information! \$\endgroup\$ – syb0rg Jul 7 '16 at 20:56
  • \$\begingroup\$ @syb0rg No problem. However, my last edit that was rolled back by Vogel didn't change any code, it just provided an example for what I already posted. Am I incorrect about that? \$\endgroup\$ – Charlie Jul 7 '16 at 21:19
  • \$\begingroup\$ According to what you are saying, hash is the primary key. Do you have an actual unique index specified using this table I ran into some circumstances where MySQL wanted the indices specifically stated instead of implied by the primary key clause. In addition, using INSERT IGNORE ( dev.mysql.com/doc/refman/5.5/en/insert.html ) seems questionable. Are you saying that you are deliberately allowing primary key violations? \$\endgroup\$ – Bradley Ross Jul 7 '16 at 22:17
1
\$\begingroup\$

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 
\$\endgroup\$
  • \$\begingroup\$ Because that only updates the id of the row(s) where the hash overlaps. I need to update all rows with the returned ids. For instance, in my example, if hash1 and hash4 are added to the table again, I don't want to only update the row 1 and 4. Instead I need to select all rows that match those ids and update everything to the new value. \$\endgroup\$ – Charlie Jul 7 '16 at 20:06
  • \$\begingroup\$ Perhaps you should share your "collection of updates to run after everything is inserted:" If I just look at the query in the context of being a query I don't see why you shouldn;t use it. Can you show the context of the update query? \$\endgroup\$ – dbmitch Jul 7 '16 at 20:13
  • \$\begingroup\$ Now I'm really confused - you've changed the table and the contents for hash field from a text to a number - still incremental I guess, then you've also changed the update query. Why wouldn't you include '3' in your query if you want it updated? You didn't include 'hash3' or 'hash4' in your original example. \$\endgroup\$ – dbmitch Jul 7 '16 at 21:13
  • \$\begingroup\$ The type change was inadvertent, just quicker to type but doesn't really change anything either. And because I don't know if hash3 (3) exists. All I know is that hash1 (1) and hash2 (2) exist. However, there might be other rows that share the id's of hash1/hash2 which I want to update as well (should they exist, which is what my query takes care of). \$\endgroup\$ – Charlie Jul 7 '16 at 21:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.