CODEWITHSUNDEEP
mysqlperl
Gordon
Gordon

Reputation: 1651

MYSQL: mass update data into existing table

I got a huge performance issue for my uploading data into mysql db. Using an example, I have special tools to mine say personal information of thousands of people.

I have one tool that mines the phone numbers of the people. Another that mines say the home address of the people. Another mines the photos of the person. So for this example, say there are 100000 people of Country A. I will have to mine data from different countries later on. These mining tools will finish at different times. The mining of phone numbers takes 20 mins. Mining of photos takes 1 week. Mining of the addresses takes 3 days.

The customer wants to see the data as soon as possible in an existing table/db. I wrote some scripts to detect when one tool finishes to start uploading row by row data. However, this seems to take a REALLY long time (using UPDATE ...).

Is there a faster way to do this?

The table that exists in the db is structure like this:

Columns: ID_COUNTRY,ID_PERSON,FULL NAME,PHONE,BLOB_PHOTO,ADDRESS

Upvotes: 0

Views: 95

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269963

Yes, there is a faster way. Put the data from each of the processes into a separate table, by inserting into the table.

You will then have to create a query to gather the data:

select *
from people p left outer join
     phones ph
     on p.personid = ph.perhsonid left outer join
     addresses a
     on p.personid = a.personid left outer join
     photos pho
     on p.personid = pho.personid;

Each individual table should start off empty. When the results are available, the table can be loaded using insert. This has at least two advantages. (1) inserts are faster than updates, and bulk inserts may be faster still. (2) The data is available in some tables without blocking inserts into the rest of the tables.

Upvotes: 1

Related Questions