Efficient MySQL Loop?

[dopes242]

Need some feedback on this loop

 

Tables:

Table A

Table B

 

Loop

Select all from A based on criteria

a. Insert into table B using row data from table A

b. Update record in table A to say that it has gone through loop to prevent future loop

 

 

Thats three MySQL queries running over and over at the same time, how bad is that for performance, is there another way?

 

Maybe instead of running b each time build an array with the record ids from Table A and after the loop runs to insert into Table B run another query with the IN command to loop through all of the IDs in the array and update?

[Pikachu2000]

Why is it necessary to store the same data in two tables to begin with?

[dopes242]

That is actually a really good question that is making me wonder now.

 

Background:

Table A is a user profile (20+ columns)

Table B is table that holds their email addresses and values on if they can be spammed or not,

 

I guess in the beginning I was looking at table B being a separate feature so I made a new table but I guess I could just add Table B's important fields to table A.

 

I guess technically there is no need to copy the information over, thank you Pikachu2000, as easy as that was you ma have just made my work and the servers work a lot easier.

[dopes242]

But looking at my next page of code I will have to use a similar loop that will

1. add different data to a queue table

2. update table A to say that it has been queue.

 

 

(1 look through user profiles who havent been sent a welcome message (welcome_message=null)

2. add user id to to que with welcome message id

3 update user profile to say welcome message has been queue)

[kicken]

You could probably just do an INSERT ... SELECT query followed by an UPDATE query.

 

INSERT INTO queue_table (
   ..fields..
)
SELECT
   ..fields..
FROM users_table
WHERE
    welcome_message is null

 

UPDATE users_table
SET
    welcome_message=1
WHERE
    welcome_message is null

 

[dopes242]

Kicken, im going to be moving about 4 to 5 fields with varchar(5-20) field lengths,

 

Because the loop was going to be running three queries I was going to add a 'LIMIT 1000' the original Select from Table A.

 

Do you think the INSERT INTO is efficient enough to double that limit or remove it?

 

(My scripts are only allowed to run for 5 minutes)

[dopes242]

Also I need to make sure that both work on the same records, in perfect world in the time it takes to carry out both actions no users will edit data.

 

Example, the query will only work on the records that have a email value set, if the insert into runs past a record and a user updates a record to include a email address before the update has ran there will be an issue, chances are small but I rather not run into that problem if I dont have to,

 

Should I:

Use a transaction?

Find someway of locking that table?(no clue there)

[scootstah]

The MyISAM storage engine uses table locks.

 

I still don't understand what you are trying to do, but I guarantee it could be done with a single more complex query.