MySQL Forums
Forum List  »  Performance

Re: Performance of ON DUPLICATE KEY and other options?
Posted by: Mark Modrall
Date: September 26, 2005 02:30PM

Hi Jay...

Just to try and give back some results from this discussion, I tried using the MD5(input) column/unique index in the table design and ran the same test several times. It always cloced in at about 30-35% slower than the original. I'm guessing that it was either
a) cpu load on the mysql server for calculating the md5 outweighed the other advantages or
b) the average length of my real input made the md5 either a break-even or a net loss (ie if the md5 is 32 characters and my average input length is 32 bytes or less, I haven't gained anything).

To see if I could improve on that, I moved the md5 calculation off of the mysql server and into my program. That way I could offload the cpu expense plus I could get the 16 byte binary representation and store it as a TINYBLOB UNIQUE making my average payoff line that much smaller.

Just running some more tests on that, but that approach seems to be a near 20% speed improvement vs a 30% speed loss.

Thanks for the idea.

-mark

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Performance of ON DUPLICATE KEY and other options?
1500
September 26, 2005 02:30PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.