Insert 20K records in 3 tables with validation in less time

Question

I have 3 tables customers, customer_address and customer_org with 20K records and I want to insert a new record with checking 'Email' of new record with table customers 'Email' attribute already exist or not.

To insert 17K records it is taking 1Hr 21Min.

I'm inserting a new record in all 3 tables, if the new record Email does not exist in customers table. So is there any solution to reduce this time?

I'm using Laravel framework.

Database: mysql innodb

See https://stackoverflow.com/a/11844438/4604701 and https://stackoverflow.com/a/1361368/4604701 — fubar, Jan 05 '18 at 05:20
Your solution looks optimal, but then again, we can't see your solution, so how would we know? — Strawberry, Jan 05 '18 at 08:20

score 0 · Answer 1 · answered Jan 10 '18 at 12:02

0

Solution: 1> create a cron job for it which automatically takes care of it without user intervention.

2>Use SAS disk or SSD's for MySQL database.

answered Jan 10 '18 at 12:02

Dev_lover

1
2

score -1 · Answer 2 · answered Jan 05 '18 at 05:38

It's not clear what your schema is including it's constraints and indexes or what your actual queries / statements are. It's impossible to answer your question without this information, and without any profiling data.

You would do well to read up on MySql Profiling, as this will help you to find out what is going on with your queries / statements.

If I were to hazard a guess, however, I would guess that the 'customers.email' column is not properly indexed.

Insert 20K records in 3 tables with validation in less time

2 Answers2