Link to home
Start Free TrialLog in
Avatar of Insoftservice inso
Insoftservice insoFlag for India

asked on

Query related to MYSQL INNODB Flow

Have few queries related to MYSQL had google but did not got proper answer for the same.
1> There are three tables say A,B,C inter linked with each other .i.e A.id =B.a_id , B.id=c.b_id
Now if There sequential is insertion in A with ID "100" and then B but while inserting in C there is some issue and there is rollback so Does all record or A and B are revoked. If No then how does it follows Rollback facility. If yes then if there is insertion A then what would be its auto incremented value will it be 101 or 100 as previous 100 records is rollback.

2> These three table has huge data (2,000,000 + records in each table ) with paid and unpaid(how did just window shopping + who put the products in wish list ) user based on order no . I would say similar to AMAZON and i have to provide daily report of Paid user and unpaid user to mgmt. what method i have to use will it be plain mysql or some thing like hadoop or some other method has to be used.
Please guide as its taking huge time to fetch data all tables are normalized.
Avatar of David Favor
David Favor
Flag of United States of America image

1) If you wrap all your logic in a single transaction, any error in that transaction should abort all actions within the transaction.

2) I have a similar set of code I developed with a partner. This code manages 1,000,000s of email leads for 10,000s of clients, who purchase leads from an inventory. Clients may also upload suppression lists of email they already have in various lists. Suppressions are then applied before the next batch of leads are delivered.

This is a similar situation to your case, as some clients may provide 100,000s of suppressions.

The way I manage this is to convert database row numbers into IntSpans (ranges of integers) using http://search.cpan.org/~swmcd/Set-IntSpan-1.19/IntSpan.pm which allows much faster processing at various points in the lead generation + reporting pipeline.

This might assist you also.

Likely you'll have better assistance from people if you post...

1) Your current database schema.

2) Examples of your current queries.
Avatar of Insoftservice inso

ASKER

Thx david. I you could provide some more links on it. It would be great
Sorry i can't provide you DB scehma. But what normally DBA do if there records are more than (2,000,000 + records in each table ) and they have to join 3-4 tables in each query.
There might be some alternate way to do it
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.