Solved

can mysql handle over 10 mil of rows?

Posted on 2014-09-23
6
800 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 108

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

746 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

12 Experts available now in Live!

Get 1:1 Help Now