Solved

can mysql handle over 10 mil of rows?

Posted on 2014-09-23
6
820 Views
Last Modified: 2014-09-23
Dear Experts,

I wish to know if mysql can handle more than 10 mil of rows? I have a query coded in to php simply obtain 10 mil rows of data from mysql DB1 to mysql DB2. DB1 and DB2 are from different location (2 different servers). This is a table with 10 mil rows and this is the only table that I cannot copy.

I wish to know if mysql/php can handle over 10 mil of rows as in copy all 10mil of rows with a single connection? (If I put a LIMIT 100000 my code will be able to copy all those rows over).
0
Comment
Question by:Kinderly Wade
6 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 40340384
Why not dump the db, copy it over and reimport?
There is no limit to what you can copy, the only problem would be the way you are doing it.
0
 

Author Comment

by:Kinderly Wade
ID: 40340398
I've created a script that will execute the backup every hour. Since the code is based upon PHP, I am just trying out with the php solution.

I am just wondering if there is a cap on number of rows the database can copy over with a single connection because if I placed a LIMIT, and loop it through then I will be able to copy it over successfully.

If there is a cap, was it capped by my web hosting provider or simply there is a cap within mysql as in number of rows can be copied with a single connection?
0
 
LVL 58

Expert Comment

by:Gary
ID: 40340402
Is this run from the commandline?
Give me some idea of the sql - you're not trying to load 10 million records in one go are you?
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 109

Accepted Solution

by:
Ray Paseur earned 350 total points
ID: 40340417
The answer about 10,000,000 rows is "no problem" in the MySQL side.  But the process may take a long time, and there may be server-to-server communications issues we don't know about.  I would think you want to come up with a restartable process.  Usually these are based on a UNIQUE column value.  When an attempt to INSERT a duplicate value in a UNIQUE index occurs, MySQL throws error #1062.  Your script can trap this error and ignore it.  Another way to do this is to run several steps to copy the data, perhaps using LIMIT clauses to reduce the size in any one operational step.
0
 
LVL 9

Assisted Solution

by:Brian Tao
Brian Tao earned 150 total points
ID: 40340454
MySQL has no problem handling multi-million records. I believe it's on PHP side.  It should be that your script hit either execution time limit or memory limit.
So when you said you could not copy, what was the error message you got from your PHP script?
0
 

Author Closing Comment

by:Kinderly Wade
ID: 40340505
I think it is the limitation from web hosting site. I was able to do 10 mil locally by setting up two mysql DB on separate machine. If I try to connect to web hosting DB, I get some sort of cap around 100K rows. Thanks all for great feedback.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Creating and Managing Databases with phpMyAdmin in cPanel.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now