Hello everybody
I'm facing a problem of performance: i'm managing packages of
thousands of products, that will be loaded in almost 10 different
table.
I won't bore you with the details, but I have to decide wether perform
a

SELECT FROM TABLE (unsing the index)
IF FOUND THEN
UPDATE TABLE
ELSE
INSERT INTO TABLE

or a

DELETE FROM TABLE (using the index)
INSERT INTO TABLE


In my case the result will be exactly the same, but (because of the
number of rows) I'd like to know if one of the two way will perform
better than the other.

If you need more infos, just ask!
thanx!
korovev

On Fri, 28 Dec 2007 09:51:59 -0800 (PST), korovev76@gmail.com wrote:
> Hello everybody
> I'm facing a problem of performance: i'm managing packages of
> thousands of products, that will be loaded in almost 10 different
> table.

For "thousands", I'd not worry too much about performance -- you might
save a couple of seconds, but probably not even tens of seconds.

> I won't bore you with the details, but I have to decide wether perform
> a
>
> SELECT FROM TABLE (unsing the index)
> IF FOUND THEN
> UPDATE TABLE
> ELSE
> INSERT INTO TABLE
>
> or a
>
> DELETE FROM TABLE (using the index)
> INSERT INTO TABLE
>
>
> In my case the result will be exactly the same, but (because of the
> number of rows) I'd like to know if one of the two way will perform
> better than the other.

INSERT ... ON DUPLICATE KEY UPDATE is a cleaner syntax and does exactly
what you're trying to do. I'd presume that some effort has been spent on
making it as fast as reasonably possible, and at definately at least as
fast as trying to do it by some kind of construction.

--
"... I've seen Sun monitors on fire off the side of the multimedia lab.
I've seen NTU lights glitter in the dark near the Mail Gate.
All these things will be lost in time, like the root partition last week.
Time to die...". -- Peter Gutmann in the scary.devil.monastery

On Fri, 28 Dec 2007 09:51:59 -0800 (PST),
korovev76@gmail.com wrote:

>Hello everybody
>I'm facing a problem of performance: i'm managing packages of
>thousands of products, that will be loaded in almost 10 different
>table.
>I won't bore you with the details, but I have to decide wether perform
>a
>
>SELECT FROM TABLE (unsing the index)
>IF FOUND THEN
> UPDATE TABLE
>ELSE
> INSERT INTO TABLE
>
>or a
>
>DELETE FROM TABLE (using the index)
>INSERT INTO TABLE
>
>
>In my case the result will be exactly the same, but (because of the
>number of rows) I'd like to know if one of the two way will perform
>better than the other.
>
>If you need more infos, just ask!
>thanx!
>korovev

You're looking for REPLACE, which does exactly what you
want (if your PRIMARY KEY or UNIQUE INDEXes are Ok).
--
( Kees
)
c[_] Virtue is its own punishment. (#240)