On 13 Feb, 10:33, rob4you <KIEDIM...@KIEDIMELO.IT> wrote:
> > UPDATE `simple_table` SET `check` = 0;
> > INSERT INTO `simple_table` (`id,`description`,`check`) VALUES
> > (1,'FRED',1)
> > ON DUPLICATE KEY SET `check` = 1;
> > DELETE FROM `simple_table` WHERE `check` = 0
>
> Thank you for your reply.
>
> On an italian ng they've suggested me the following solution:
>
> CREATE TEMPORARY TABLE `temp` (`id` int primary key, `descr` text);
>
> INSERT INTO `temp` (`id,`descr`) VALUES (1,'FRED');
> INSERT INTO `temp` (`id,`descr`) VALUES (2,'BOB');
> -- ...
>
> TRUNCATE `simple_table`;
> INSERT INTO `simple_table` SELECT * FROM `temp`;
>
> So according to them the solution does not consider any "check" field.
> They told me this is better because in your solution MySQL has to
> perform twice a full-scan (both with the UPDATE and with the DELETE),
> and also a check on the INSERT.
>
> What do you think about it?
>
> --
> rob4you

1) I think that in your case my INSERT ... ON DUPLICATE KEY ... is
much more efficienct than using the REPLACE syntax.
2) I think that INSERTING data into a temporary table and then copying
all that data via inserts is likely to be far more inefficient than
deleting from themain table, unless there are a very large number of
deletes compared to inserts.
3) I think that an INDEX on the check field will save a full table
scan the second time.
4) I think (and this to me is the killer): Using the temporary table
method, for he period of time between the TRUNCATE and the completion
of the second INSERT, your real table is not available for use as all
the records that should be there are missing for a varying period of
time. Using the check method, the required records are always there.
5) I think assuggested above it depends on things like quantities of
data to be inserted/refreshed/deleted and availability questions, none
of which you have given any indication about here. Both ideas will
work, but we do not have enough data to indicate which is better in
your case.