Search the Community

Hi, I got a requirement where I need to define a MySQL database setup (using two databases if best which is reason for question). Here's the objective: 1st (primary task): setup a MySQL db that will allow multiple websites (email capture forms) to log or save emails to that database (with minimum latency). Note: form validation, per website, will ensure the emails are properly formatted so only MySQL INSERT required for this (1st) db - no other processing in this regard. 2nd (secondary task): That 1st MySQL db which will be under constant load - receiving emails from various sites (this hourly number could average between 10 to twenty-five thousand which is why I need to offline this task solely to one db <I think>). However, as new emails are logged to this (1st) db, I need to then batch those saved records over to a 2nd db which will be performing a series of functions - processing the leads along with number crunching (data mining, etc...) Illustration the setup below: once record logged in DB1 (which is required to house ALL data - long-term; DB2 only housing a short-interval of incoming records - this to reduce processing on new entries). <Objective: get records over to DB2 without hampering DB1 on incoming INSERTS from websites> DB1 ===> DB2 SQL INSERT (Objective: DB1 needs to pass records over to DB2 while still capturing incoming emails with the lowest possible latency for end-user). ----- Q: Should I setup transactions between DB1 & DB2 to pass the records into DB2 which - i think would induce latency back on DB1 b/c it will pause taking new emails while waiting for DB2 to accept or ignore new entries to that DB (FYI: DB2 will 'ignore' duplicate email entries). Or Is there a better way to batch the records from DB1 over to DB2 in a way DB1 won't get bogged down with that process so it continues to receive new emails - whcih will be constant and in high volume. Comments welcome and I will elaborate more if/when required to help understand the objective. Thanks!