Thread: TWO SAME TABLES, ONE UPDATED. HOW TO SYNC THE OTHER?
Hello I have two tables -- A and B. The structure of both is the same. Only, B has many indexes and is used for heavy duty SELECTs. On the other hand, A only accepts heavy duty INSERTs, so has only one primary key index. So my DB design is such that A is only an INSERT table. Periodically, say every 20 minutes or so, I would like to take all the new INSERTs from table A and put them into B. Is there any clever command to accomplish this? I'd rather not write a PHP script with SQL to take every single new record, and update every column of a new row in table B. For instance, can I do a replication of only tables, not databases? Thanks for any pointers!! LB -- Conan O' Brien gets it right! http://blogs.pcworld.com/tipsandtweaks/archives/004369.html
On May 11, 11:06 pm, "L. Berger" <straightfwd...@gmail.com> wrote: > Hello > > I havetwotables-- A and B. The structure of both is thesame. Only, > B has many indexes and is used for heavy duty SELECTs. On theother > hand, A only accepts heavy duty INSERTs, so has onlyoneprimary key > index. > > So my DB design is such that A is only an INSERT table. Periodically, > say every 20 minutes or so, I would like to take all the new INSERTs > from table A and put them into B. > > Is there any clever command to accomplish this? I'd rather not write a > PHP script with SQL to take every single new record, and update every > column of a new row in table B. For instance, can I do a replication > of onlytables, not databases? > > Thanks for any pointers!! > > LB Assuming ID is PK: INSERT INTO b SELECT * FROM a WHERE NOT EXISTS ( SELECT 1 FROM b WHERE b.ID = a.ID )
This query will run quite slow if tables are large, so - you may in addition create a trigger-updated TIMESTAMP columns and search for changed data through the recent created/updated elements only.
On 13 May 2007 02:21:30 -0700, rdeleonp@gmail.com <rdeleonp@gmail.com> wrote:
On May 11, 11:06 pm, "L. Berger" <straightfwd...@gmail.com> wrote:
> Hello
>
> I havetwotables-- A and B. The structure of both is thesame. Only,
> B has many indexes and is used for heavy duty SELECTs. On theother
> hand, A only accepts heavy duty INSERTs, so has onlyoneprimary key
> index.
>
> So my DB design is such that A is only an INSERT table. Periodically,
> say every 20 minutes or so, I would like to take all the new INSERTs
> from table A and put them into B.
>
> Is there any clever command to accomplish this? I'd rather not write a
> PHP script with SQL to take every single new record, and update every
> column of a new row in table B. For instance, can I do a replication
> of onlytables, not databases?
>
> Thanks for any pointers!!
>
> LB
Assuming ID is PK:
INSERT INTO b
SELECT *
FROM a
WHERE NOT EXISTS (
SELECT 1
FROM b
WHERE b.ID = a.ID
)
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match