Bash script with mysql

[Silver-0-surfer]

Hi all

I have written a bash script to do this for me.

I have a table with about 150 000 usernames in it. I would like to divide them up into groups of 1000 and the assign a value depending to what group they are in for example.

the first 1000 users will be group 1, then next 1000 are group 2 and so on. This is what I have written.
<code>
echo "Enter the file name"
read users

index=0
count=0
while read line ; do

if [ $(($index%1000)) -eq 0 ]; then
let count=count+1

#a=$(($index%1000))
#echo $a
echo $count
MYARRAY[$index]="$line"
index=$(($index+1))
echo "update table set group='$count' where user='$line';" | mysql database;



else


#a=$(($index%1000))
#echo $a
#echo $count
MYARRAY[$index]="$line"
index=$(($index+1))
echo "update table set group='$count' where user='$line';" | mysql database;

fi
done < $users

</code>

Now from what I can tell it works. (I am not very skilled at this tbh) but it is taking FOREVER. its been goin since around 10am and its at around group 40 atm.

Does anyone know why its so slow?maybe I can tweak it to get better performace?

What do you guys think?

 

[Nod]

What does your schema look like? Do you have an id field?

Maybe one could use the id field to decide whether to place a user in a certain group.

 

[Other Pineapple Smurf]

your "echo $count" will spool to your screen, this can slow down your script (from personal experience)

Rather output to a log file that you can "tail -F" instead.

 

[Silver-0-surfer]

Yes i do have an Id field although i can't do that because there is actually two tables with usernames. once I have them arrange into smaller groups I then can disable users in the real usernames table against the the group i defined in the new table with group name....I hope that makes some sense.

The reason I do it this way is because there is a license in place on the real usernames table that only allows a certain amount of username bieng active (there is also an active coloumn) so I need to enable & disable them based on some sort of logical order (group numbers). anyway its not an issue, I just wanted to know why Its so slow so that I can maybe learn better(faster) ways of inputting/updating info in mysql

 

[Silver-0-surfer]

I thought that might be an issue, altough its only doing the echo every 1000 users. its not like 111111111222222222 its like 123. you think that can really slow it down that much?

 

[wmaker]

your "echo $count" will spool to your screen, this can slow down your script (from personal experience)

Rather output to a log file that you can "tail -F" instead.

+1

Another approach: you can split your input file into separate files using "split" - the default split is 1000 lines per file
Then for each file, prefix the name with your update statement (using eg. sed) and feed these files to mysql. Create all the files first before you call mysql. Remember, update takes longer than for example select.