Professional Web Applications Themes

insert if not exist and delete old rows - MySQL

Hi to everyboby ;-) i've a simple table with two (or three, according to what you think about it :-) ) fields: id | description | check I periodically (say, every day, or every hour, or what you prefer) read somewhere the information to store in this table. Well, if the row is already present in the table (that is if a record with the same id is already stored), i dont want to insert it again (in order to save time, and resources, isnt it?). Otherwise I'll insert as a new record. Moreover, I'd want to insert a "check" ...

  1. #1

    Default insert if not exist and delete old rows

    Hi to everyboby ;-)

    i've a simple table with two (or three, according to what you think
    about it :-) ) fields:

    id | description | check

    I periodically (say, every day, or every hour, or what you prefer) read
    somewhere the information to store in this table.
    Well, if the row is already present in the table (that is if a record
    with the same id is already stored), i dont want to insert it again (in
    order to save time, and resources, isnt it?).
    Otherwise I'll insert as a new record.

    Moreover, I'd want to insert a "check" field so that:
    - if the record is already in the table I wont insert it again (but
    i'll check that it's a good record),
    - if the record is not in the table I'll insert it (and I'll check that
    it's a good record), and
    - at the end I want to delete old records, that is all the records that
    I'v not checked.

    Any idea about how to complete this task?

    Thank you.

    --
    rob4you


    rob4you Guest

  2. #2

    Default Re: insert if not exist and delete old rows

    On 12 Feb, 11:53, rob4you <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

    Captain Guest

  3. #3

    Default Re: insert if not exist and delete old rows

    UPDATE `simple_table` SET `check` = 0;
    REPLACE `simple_table` (`id,`description`,`check`) SET id = '1',
    description='aaa', check='1'
    DELETE FROM `simple_table` WHERE `check` = 0

    ---
    http://ascii.mastervb.net -- ASCII Art Generator
    http://anagram.mastervb.net -- Anagram Finder
    http://www.mastervb.net/phpbooks -- Best PHP Books

    On Feb 12, 6:53 pm, rob4you <IT> wrote: 


    lorento Guest

  4. #4

    Default Re: insert if not exist and delete old rows

    > UPDATE `simple_table` SET `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


    rob4you Guest

  5. #5

    Default Re: insert if not exist and delete old rows

    > UPDATE `simple_table` SET `check` = 0; 

    Thank you for you reply.

    Please read my reply to Captain Paralytic, and if you want, let me know
    what you think about that.

    --
    rob4you


    rob4you Guest

  6. #6

    Default Re: insert if not exist and delete old rows

    On 13 Feb, 10:33, rob4you <IT> wrote: 
    >
    > 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[/ref]

    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.

    Captain Guest

  7. #7

    Default Re: insert if not exist and delete old rows

    > I think ...

    Thank you. Your comments are very helpfull.

    Well, I'll try to explain better the situation:

    periodically (say each two hours), my php script searches on a foreign
    website for some particular 'text', and inserts them in my db. These
    'texts' can be more less 10000.
    Two hours later, my php script searches again all the particular
    'texts'.
    These 'texts' will be again 10000 more less. Probably, the new one will
    be just 10.

    So, i've to perform these operations on the db:
    - insert all the new 'texts'
    - delete all the old 'texts', that is the 'texts' that my php script
    doesnt find anymore in the site. These old 'texts' will be 10-20, so a
    few number
    - of course mantain the still available 'texts', that is the 'texts'
    that have already been inserted previously in the db, and are still
    present on the site.

    Is is clearer now?
    Which is the optimal solution for this case?

    --
    rob4you


    rob4you Guest

  8. #8

    Default Re: insert if not exist and delete old rows

    On 13 Feb, 11:52, rob4you <IT> wrote: 
    >
    > Thank you. Your comments are very helpfull.
    >
    > Well, I'll try to explain better the situation:
    >
    > periodically (say each two hours), my php script searches on a foreign
    > website for some particular 'text', and inserts them in my db. These
    > 'texts' can be more less 10000.
    > Two hours later, my php script searches again all the particular
    > 'texts'.
    > These 'texts' will be again 10000 more less. Probably, the new one will
    > be just 10.
    >
    > So, i've to perform these operations on the db:
    > - insert all the new 'texts'
    > - delete all the old 'texts', that is the 'texts' that my php script
    > doesnt find anymore in the site. These old 'texts' will be 10-20, so a
    > few number
    > - of course mantain the still available 'texts', that is the 'texts'
    > that have already been inserted previously in the db, and are still
    > present on the site.
    >
    > Is is clearer now?
    > Which is the optimal solution for this case?
    >
    > --
    > rob4you[/ref]

    OK, let's take what you've said:
    Every 2 hours you will collect 10000 records of which 10 are new.
    Using the temp table method you will INSERT 10000 records into one
    table, you will then copy those 10000 records from that table and
    insert them into another table.

    Using my method you will update just under 20000 records and insert 10
    new ones
    So far my method has moved half the amount of data of the other method
    as the initial delete will not actually have to move any data.

    Assuming that you have an index on the check field, with my method you
    will now use it to simply delete 10-20 records.

    During all this, with my method the database is available for use to
    read, whereas with the other method means that it is not available for
    a period.

    I think my one wins in this scenario.

    Captain Guest

  9. #9

    Default Re: insert if not exist and delete old rows

    > Assuming that you have an index on the check field, with my method you 

    In the specific, how do you suggest to set the index for the check
    field?
     

    I'll try both methods, and i'll see which is completed in less time.
    They told me that your method is heavy because of the twice full-scan,
    but as you suggest, with the index it should become lighter.

    --
    rob4you


    rob4you Guest

  10. #10

    Default Re: insert if not exist and delete old rows

    On 13 Feb, 12:07, rob4you <IT> wrote: 
    >
    > In the specific, how do you suggest to set the index for the check
    > field?

    >
    > I'll try both methods, and i'll see which is completed in less time.
    > They told me that your method is heavy because of the twice full-scan,
    > but as you suggest, with the index it should become lighter.
    >
    > --
    > rob4you[/ref]

    My method moves far less data and thus is likely to be far better in
    this case.

    I don't understand what you mean by how to set the index for the check
    field?

    Please try to re-phrase.

    Captain Guest

  11. #11

    Default Re: insert if not exist and delete old rows

    > I don't understand what you mean by how to set the index for the check 

    You told me:
     [/ref][/ref]

    Well, what do i have to do for setting up/altering my table for
    indexing the check field?

    --
    rob4you


    rob4you Guest

  12. #12

    Default Re: insert if not exist and delete old rows

    On 13 Feb, 13:07, rob4you <IT> wrote: 
    >
    > You told me:
    > [/ref]
    >
    > Well, what do i have to do for setting up/altering my table for
    > indexing the check field?
    >
    > --
    > rob4you[/ref]

    When you said what do I suggest, I thought you were asking about any
    special attributes of the index.

    What you should do to set up the index is something very simple, it is
    what I tend to do often when I need to know how to do something. Read
    the Manual!

    ALTER TABLE `simple_table` ADD INDEX ( `count` )

    Captain Guest

  13. #13

    Default Re: insert if not exist and delete old rows

    > ALTER TABLE `simple_table` ADD INDEX ( `count` )

    I supposed this was what I had to do, but I thought there was something
    other to specify. But I was wrong. :-)

    Thank you. I'll try everything and later I'll post the results.

    --
    rob4you


    rob4you Guest

  14. #14

    Default Re: insert if not exist and delete old rows

    On 13 Feb, 14:45, rob4you <IT> wrote: 
    >
    > I supposed this was what I had to do, but I thought there was something
    > other to specify. But I was wrong. :-)
    >
    > Thank you. I'll try everything and later I'll post the results.
    >
    > --
    > rob4you[/ref]

    Yes, it will be interesting to know what the numbers end up looking
    like.

    Captain Guest

  15. #15

    Default Re: insert if not exist and delete old rows

    > Yes, it will be interesting to know what the numbers end up looking 

    Hi, I've had another idea:

    instead of using a 'check' field as "int", I could declare it as
    "timestamp".
    I could do, then:

    SELECT t:=NOW();

    INSERT INTO `simple_table` (`id,`description`,`check`) VALUES
    (1,'FRED',1)
    ON DUPLICATE KEY UPDATE `check` = NOW();

    DELETE FROM `simple_table` WHERE `check` < t;

    By this way I'd eliminate the first operations you suggested me, that
    is:
    UPDATE `simple_table` SET `check` = '0';

    What do you think about that?

    ps: is it valid, still in this case, the idea of indexing the 'check'
    field?

    --
    rob4you


    rob4you Guest

  16. #16

    Default Re: insert if not exist and delete old rows

    On 14 Feb, 15:54, rob4you <IT> wrote: 
    >
    > Hi, I've had another idea:
    >
    > instead of using a 'check' field as "int", I could declare it as
    > "timestamp".
    > I could do, then:
    >
    > SELECT t:=NOW();
    >
    > INSERT INTO `simple_table` (`id,`description`,`check`) VALUES
    > (1,'FRED',1)
    > ON DUPLICATE KEY UPDATE `check` = NOW();
    >
    > DELETE FROM `simple_table` WHERE `check` < t;
    >
    > By this way I'd eliminate the first operations you suggested me, that
    > is:
    > UPDATE `simple_table` SET `check` = '0';
    >
    > What do you think about that?
    >
    > ps: is it valid, still in this case, the idea of indexing the 'check'
    > field?
    >
    > --
    > rob4you[/ref]

    The index is still valid and you'll have to insert t (instead of 1)
    and update with t

    Captain Guest

  17. #17

    Default Re: insert if not exist and delete old rows

    SELECT t:=NOW();

    INSERT INTO `simple_table` (`id,`description`,`check`) VALUES
    (1,'FRED',NOW())
    ON DUPLICATE KEY UPDATE `check` = NOW();

    DELETE FROM `simple_table` WHERE `check` < t;
     

    I think i can also use NOW() instead of t, like in my example.
    By this way the timestamp would be more accurate (I mean it would refer
    exactly to the time of inserting).
    Why do you think it's better to update with t all the records instead
    of NOW()? Is it faster?

    --
    rob4you


    rob4you Guest

  18. #18

    Default Re: insert if not exist and delete old rows

    On 14 Feb, 17:23, rob4you <IT> wrote: 
    >
    > I think i can also use NOW() instead of t, like in my example.
    > By this way the timestamp would be more accurate (I mean it would refer
    > exactly to the time of inserting).
    > Why do you think it's better to update with t all the records instead
    > of NOW()? Is it faster?
    >
    > --
    > rob4you[/ref]

    It may be a bit faster, but using t also becomes a unique identifier
    for a single run. I was really pointing out that inserting 1 as the
    check value would not work.

    Captain Guest

Similar Threads

  1. insert values only if they do not already exist in table
    By kabbi~thkek in forum Coldfusion Database Access
    Replies: 6
    Last Post: February 8th, 07:14 AM
  2. Howto insert delete rows into a ASP.net datagrid?
    By Jimmy in forum ASP.NET Data Grid Control
    Replies: 3
    Last Post: April 21st, 04:58 AM
  3. Unable to delete connection - unpublished drafts exist... but don't
    By Ellie in forum Macromedia Contribute General Discussion
    Replies: 1
    Last Post: March 30th, 05:39 PM
  4. INSERT did not insert correct # of rows.
    By Polaris in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: August 3rd, 02:46 AM
  5. Select rows where other related rows don't exist
    By Dmand in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 2nd, 06:23 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139