mysql query optimization problem

Question

I am trying to import data from a large database. I have two tables with couple of hundred thousands records. I have to search data in two tables then insert that record in a new table (3rd table), if the record already exists in 3rd table then I have to update one column record in 3rd table.

This sounds easy, but it is taking so long to process.

Below are sample queries and pseudo code:

select * from table1 INNER JOIN table2 USING(id)

search in table 3 -> 
 if record exist{
  update record in table 3 (update counter in a column)
 }else{
  Insert new record in table 3
 }

First and second table has more than two hundred thousands records. As I start inserting record in 3rd table it kills the whole speed because then it also have to search in 3rd table to update or insert a record.

Database Name = MySql
Language = Php

What is the problem? How can I improve this? I can not wait hours to process it :(

Thanks

EDIT:

In table 3, id has primary key and all other columns are normal. Database schema is too big plus complex. Do you guys want , I paste hundred of lines here?

Can you please guys point out mistake in my pseudo code and query? What index or structure I can use to improve performance??

Structure

Table 1 - usr_id, first name, last name (usr_id is primary key)
Table 2 - id, usr_id, amount (id is primary key and usr_id is foreign key)

Table 3 - new_id , first name, last name, usr_id, total_amount (new_id is primary key and usr_id is foreign key

)

I check if table 3 has same first name and last name then update total amount, if they are different then insert a new record

Can you post your DB schema? It doesn't sound like table3 is indexed correctly. — jasonbar
– jasonbar, Commented Jan 9, 2011 at 18:13
you will have to show us more about your table structures, and indexs. — dqhendricks
– dqhendricks, Commented Jan 9, 2011 at 18:13

a1ex07 · Accepted Answer · 2011-01-09 18:28:31Z

1

From your pseudo code I see that you check record existence in Table 3 for each record in select * from table1 INNER JOIN table2 USING(id). You eventually end up with thousands of select commands that degrade performance. You can improve it by

select t1.*, t2.*, t3.pk_field from table1 t1 INNER JOIN table2 t2 USING(id)
LEFT JOIN table3 t3 ON (join codition)

Now you can just test if t3.pk_field is null (insert if it's null, update otherwise). The next step to improve it is to write one query that takes care of everything , using INSERT ON DUPLICATE KEY UPDATE as proposed by jasonbar:

INSERT INTO table3(col1, col2,...) 
 select t1.col1, t1.col2, t2.col3, .... 
 from table1 t1 INNER JOIN table2 t2 USING(id)
ON DUPLICATE KEY UPDATE counter = new_value

answered Jan 9, 2011 at 18:28

a1ex07

37.4k12 gold badges93 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Tweet Over a year ago

Thanks, for table 3, I made the check if first and last name is same then update the value of current balance (with some pre-defined formula). I couldn't understand when you said t3.pk_field because my first name, last name, and balance columns are normal columns not primary or foreign key.

a1ex07 Over a year ago

I used t3.pk_field just for example (I had no idea about your table structure). It can be any field from Table3 that cannot be normally null (Null value will indicate that the record does not exist in the table, and you need to insert a record rather then update)

Tweet Over a year ago

I updated my question and tried to put simplest version of my tables to explain my problem.

Tweet Over a year ago

DUPLICATE KEY UPDATE is not working because my columns do not have any key to check duplicate :( any other solution?

a1ex07 Over a year ago

Why do you store first/last names AND usr_id in Table3 ? If Table3 has a reference to Table1.usr_id, only usr_id should be in Table3. If you are trying to create a new user table, you should store just first/last names in Table3. Also, adding [unique?] index on (table3.first_name,table3.last_name) will significantly improve performance.

jasonbar · Accepted Answer · 2011-01-09 18:09:15Z

1

You should look into the INSERT ON DUPLICATE KEY UPDATE syntax.

Something like:

INSERT INTO `table3` (`col1`, `col2`) VALUES('val1', 'val2')
ON DUPLICATE KEY UPDATE `counter` = `counter` + 1;

If a simple select query to see if the row already exists in table3 is taking too long, you probably aren't using indexes (or at least not using them correctly). We would need a fair bit more information to try and troubleshoot that.

answered Jan 9, 2011 at 18:09

jasonbar

13.5k4 gold badges41 silver badges47 bronze badges

2 Comments

Tweet Over a year ago

Can you please also point out why it is taking too long? What is a good structure to improve performance?

Tweet Over a year ago

The column I am updating is not unique nor primary key. The query, you proposed require any of the above condition. Let me update my question to put more information :)

Collectives™ on Stack Overflow

mysql query optimization problem

EDIT:

Structure

2 Answers 2

5 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

EDIT:

Structure

2 Answers 2

5 Comments

2 Comments

Related