mysql query request

hello every body ,

i have a nice forum (vBulletin 3.7.3) , before month ago i imported another friend database into mine and it became like 600 m

about the request, i want a query to excute on the ssh to delete the users that havent any post because they aren't useful for me

help me please , thanks

 

aint there a 0-poster-kill function in vb?

 

This might be more PHP: INSERT INTO table (posts, etc.) VALUES posts = '0'

 

well, i programmed php code for that , it works very well but i got one problem ,

the members are like 500,000 so when i opened the php file on the browser to excute

the codes it works and it really deleted like 10,000 member with 0 posts , but it

stoped because the members are so big , it didnt load and the browser crashed

so here is my code , please help me to find way to excute it from the ssh or help me

to fix my code to continue the deleting operation with nice fast load

------------------------------

<?
$connect = @mysql_connect($host, $dbuser, $dbpass) or die("error");
$select = @mysql_select_db($dbname) or die("error");

$limit = "500";
$i=0;
$id= 0;

while ($i < $limit) {

$result = mysql_query("SELECT * FROM user WHERE userid='$id'");
$found = mysql_num_rows($result);
$row = mysql_fetch_array($result);
$username = $row['username'];


$result2 = mysql_query("SELECT * FROM post where username='$username'");
$num = mysql_num_rows($result2);

if ($num == 0 && $found >0) {
$result = mysql_query("delete FROM user WHERE userid='$id'");
echo "user deleted: id=". "$id"."<br>";
}

$i++;
$id++;

}

?>

-------------------------------

 

hay i forgot $limit = "500000"; not $limit = "500";

waiting for the help thanks

 

so when it deletes 10.000 why not run the script 50 times?

 

it took 6 hours to load that 10,000 and the browser crashed , so it cant be

any other idea please ??

 

damn 6 hours for 10000 simple database queries??
is vb that bloated or your server so crappy? or both?

 

try to load php script like that on ur browser have to make 10000 query in the same time , u will see the real browser crash and u will see what we are talking about

 

Funny how PHP becomes the tool to use to perform one time only database maintenance. Then messing about with browser and PHP runtime limitations.

Enter the SQL console and use subselect functionality, something along the lines of
DELETE FROM user WHERE userid NOT IN (SELECT username FROM post);
This will delete all the users which are not in the post table.

If this doesn't work for you or your database server or your php requirements/limitations then please lose the wildcards (*) in your queries because you really don't need to transfer all the columns in the user and post table when you are only interested in the user and username columns.
SELECT * FROM user WHERE userid='$id'" becomes
SELECT userid FROM user WHERE userid='$id'"
AND
"SELECT * FROM post where username='$username'"); becomes
"SELECT username FROM post where username='$username'");

This might make your looped queries more efficient.