mysql

mysqladmin to kill the runaway query

Run the following commands:

mysqladmin -uusername -ppassword pr

Then note down the process id.

mysqladmin -uusername -ppassword kill pid

Advertisements

Do Not ORDER BY RAND()

If you really need random rows out of your results, there are much better ways of doing it. Granted it takes additional code, but you will prevent a bottleneck that gets exponentially worse as your data grows. The problem is, MySQL will have to perform RAND() operation (which takes processing power) for every single row in the table before sorting it and giving you just 1 row.

// what NOT to do:
$r = mysql_query(“SELECT username FROM user ORDER BY RAND() LIMIT 1”);

// much better:

$r = mysql_query(“SELECT count(*) FROM user”);
$d = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] – 1);

$r = mysql_query(“SELECT username FROM user LIMIT $rand, 1”);

So you pick a random number less than the number of results and use that as the offset in your LIMIT clause.