1000 threads query and update very slow from MySQL

Question

Example data: 1,000,000 rows

Structure: id (int), randomText(char 10), flag(boolean)

index creates for id, randomText and flag

This is mysql command:

update table_NAME set flag = 
     ( select flag from
           ( select @randomText:=randomText as 'randomText', '1' as flag
             from table_NAME where flag=0 limit 1
           ) as tmp 
     ) where randomText=@randomText;

Typically this took less than one second, but when I try to query it 1000 threads, it becomes almost 2 minutes, I tried InnoDB, MyISAM and MEMORY but no help.

I just want to get the randomText 500 at once (no dupplicate) and keep it loops.

What do you suggest?

Answer 1

Assuming randomText is unique, that query appears to get the first row where flag=0 and set it to 1, surely more easily achieved with

update table_NAME set flag=1 where flag=0 limit 1;

Or is there more you are trying to do? Is the randomText duplicated?

Answer 2

You seem to assume that more threads is better. Usually, that's not true.