Re: Performance of ON DUPLICATE KEY and other options?

[Mark Modrall]

Hi Jay...

Thanks for responding.

Jay Pipes wrote:
> That was exactly my point. There's no reason to
> go any further or try an "alternate technique" in
> this situation.

Call it newbie infatuation, but I'm new to MySql and saw the ON DUPLICATE KEY UPDATE feature when I was familiarizing myself with the db and thought it sounded cool. The only other thing I can add is that ON DUPLICATE KEY does let you put the logic into a single SQL statement/call rather than multiple calls + code logic in the language of your choice. At least on the surface, it sounded like a good idea, but without a lot of experimentation it was hard to tell if one had a performance advantage over another. I thought I'd ask here if people had done that kind of performance-related comparison.

> I understand you now. OK, here's my best advice
> for logging search terms and the associated
> items/stats for searches:
>
> a) Have only a single table, not 2 tables.
> b) Structure table with an MD% hash of the text
> term, and index off that, not the main text. This
> willl be *much* faster for lookups.
> c) Do a quick search on the hashed index before
> updating search entry stats, otherwise INSERT new
> search entry record.

Thanks for the MD5 suggestion; I hadn't thought of that. I'll give that a try.

> DELIMITER ///
> CREATE PROCEDURE insert_search_entry (IN
> search_text TEXT)
> BEGIN
> DECLARE entry_id INT;
...
> END IF;
> END ///

I'm still new to the community but my understanding was that stored procedures are in 5.0+ releases; is that correct? We're currently working on 4.1.12. Not sure if I'll be able to convince people to upgrade.

Thanks for your help.

-Mark