0

Lets assume i have two databases, both having a table 'user_feedback', sharding data between the two databases. My goal is to insert all records where Primary key Id%2=0 goes to db1.user_feedback, and Id%2=1 goes to db2.user_feedback.

Is there a condition in the INSERT statement that can allow me to do this? How can i achieve simply achieve this, both if the two databases are located on the same MySQL server, and when they both reside on a different MySQL server?

** UPDATE ** Ok, what i am trying to achieve is to have several tables in the same MySQL server to shard data and limit one table from becoming too big, hence slowing up queries on this table. The table is expected to grow very large (billion records) with heavy writes. That is the reason we have decided to resort to sharding. The table does not currently exist, but we want to create this shard solution in advance before we hit production with this table.

  • this won't get a performance gain. What are you trying to achieve? – danblack Feb 20 '19 at 8:56
  • There is no INSERT variant which inserts the records into 2 separate tables. You need 2 queries to perform your action. – Akina Feb 20 '19 at 8:57
  • @Akina, maybe you are right. So i may need some a way to first determine if Id%2 is 0 or 1, then use two queries to perform inserts appropriately based on the value provided. – giogio Feb 20 '19 at 9:31
  • maybe you are right I guarantee. INSERT syntax. Only single-table variants are documented. i may need some a way to first determine if Id%2 is 0 or 1, then No. You should do this directly in the queries. – Akina Feb 20 '19 at 9:35
  • My goal is to insert all records What is the source of these records that must be inserted? – Akina Feb 20 '19 at 9:40
0

I am extremely dubious that you have the problem you describe, but here goes:

CREATE TEMPORARY TABLE t (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    ...   (the rest of the columns)
    );

INSERT INTO t
    SELECT * FROM source;

INSERT INTO db1.x  SELECT ... FROM t WHERE id % 2 = 0;
INSERT INTO db2.x  SELECT ... FROM t WHERE id % 2 = 1;

If the data is coming from clients that are feeding the data in fast and furious, then I recommend ping-ponging between two temp tables, not between even/odd. The clients will INSERT into one temp table while a separate task is copying the other temp table into one of the two 'real' tables. Then that task will TRUNCATE the table it copied, then RENAME TABLE to atomically swap the temp tables.

I discuss that in more detail in http://mysql.rjweb.org/doc.php/staging_table

  • Hi @Rick James. Actually the problem situation is real. Might be a better way to think of solving the problem, but for now this is exactly what i want to achieve. So i will accept your answer for it works. However, wouldn't there be serious lags for the whole process to take place and eventually get data into db1.x and db2.x especially if the newly inserted data has to be read almost right away? – giogio Mar 6 '19 at 2:54
  • @giogio - How many rows per second? If under 100/sec, there is not likely to be any real lag. If over 1000/sec, it might never catch up. In between 100 and 1000, there will be a small lag, but the processing will be more efficient, hence it won't get "behinder and behinder". – Rick James Mar 6 '19 at 3:27
  • If can handle 100-100 queries per second with minimum and non-progressive lag then that should be accepted. It seems i would need an INSERT trigger to run INSERT INTO t SELECT * FROM source as soon as data is added to the source right? – giogio Mar 6 '19 at 3:39
  • @giogio - No. That leads to backlogs that it may not recover from. The ping-pong technique lets rows be buffered, then efficiently flushed. Note that handling 100 rows in a single SQL statement takes about as long as 10 statements, each handing only one row. I would expect the lag to be much less than 1 sec. when running at 100 to 1000 inserts/sec. and no Trigger. – Rick James Mar 6 '19 at 3:45
  • Then whats running the statement INSERT INTO t SELECT * FROM source in your example? Can you please shade more light on the so called ping-pong method? And for the "source" table, should it also be created with "CREATE TEMPORARY TABLE source..."? – giogio Mar 6 '19 at 4:18
0

Hi you can try the below query.

insert into db1.user_feedback as select * from db2.user_feedback where < your 
conditions>;
insert into db2.user_feedback as select * from db1.user_feedback where < your 
conditions>;
  • This would not work as both tables start off empty, and record placement in each shard will depend on whether the value of Id%2 is 0 or 1 i.e., If Id%2=0, insert into tb1.user_feeback,else if id%2=1, insert into tb2.user_feedback. – giogio Feb 20 '19 at 9:27
0

Use the stored routine:

CREATE PROCEDURE `splitinsert`( IN _param1 INT, ... )
BEGIN
  SELECT max(id) INTO @t1 FROM db1.tbl1;
  SELECT max(id) INTO @t2 FROM db2.tbl2;
  IF @t1 < @t2
    THEN 
      INSERT INTO db1.tbl (id, col1, ... )
      VALUES (@t1+2, _param1, ... )
    ELSE 
      INSERT INTO db2.tb2 (id, col1, ... )
      VALUES (@t2+2, _param1, ... )
  END IF;
END

And then:

CALL splitinsert(1234, ....);

Sure you have to initialize both tables by seed records.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.