If you have noticed there is an exclamatory sign (!) after the post title, let me tell you I have deliberately placed it to express my surprise on the whole thing which I’m going to share. I have been working with MySQL since early 2005, if I remember correctly it was version 4.1 which for the first time supported Sub Queries. I was a pure MySQL newbie back then and did not have any idea about how good or bad MySQL was. Honestly I switched from Microsoft Technologies to Open Source Technologies because my employer decided to switch to Open Source to avoid license costs, for obvious reasons I was skeptical about Free and Open Source MySQL and though very highly of MSSQL & MMC Console Microsoft offered! Since then till I faced the MySQL ORDER BY issue in Cloud I have used MySQL ORDER BY to solve all kinds of ranking problems, though I always knew MySQL sorting algorithm is not a great one, inside it uses qsort (quicksort) algorithm but if use the ORDER BY on a indexed field / key then it won’t cause much of an optimization issue. WON’T WORK when I’m doing the same in a load balanced database environment hosted out in cloud! Because if you try to perform a MySQL ORDER BY on a relatively large volume (about 100,000 rows) of data via a database load balancing server which is connected to multiple database servers in master-master mode with 0 seconds delay sync then whichever database server starts executing the query goes into a sleep or the query becomes non responsive and it results into a significant delay in syncing for other database servers connected to the same architecture. Let me tell you this is a disastrous situation when you have a relatively significant user concurrency, all the queries becomes slow and goes into a non responsive mode and you are forced to do a MySQL Shutdown & Start for all your connected database servers. Not a nice situation to have, ha! But then how do you solve your ranking problem without using a MySQL ORDER BY? The solution is PHP Array Multi Sort, which works in 3 simple steps; 1) fetch the entire data set from your MySQL table, 2) push the result set into an array in key-value paired format, 3) sort the array using array_multisort for one or more keys. Surely using PHP array_multisort has more complexity than using MySQL ORDER BY but it is a solution which won’t give you sleepless nights, it is a far stable solution too and it works for both physical servers as well as cloud servers.
Step 1:Fetch the entire data set from MySQL table
[php]
$SQL = ‘SELECT * FROM tbl_my_table’;
$RESULT = mysql_query ($SQL,$con);
[/php]
Step 2: Push the result set into an array in key-value paired format
[php]
$final_array = array();
while($row = mysql_fetch_assoc($RESULT))
{
array_push ($final_array,array( ‘id’ =>$[‘id’],’uid’ =>$row[‘uid’], ‘first_name’ =>$row[‘first_name’], ‘exp_pts’ =>$row[‘exp_pts’], ‘level_id’ =>$row[‘level_id’], ‘coins’ =>$row[‘coins’], ‘cash’ =>$row[‘cash’], ‘kill_count’ =>$row[‘kill_count’], ‘regtime’ =>$row[‘regtime’], ‘rank_date’ =>$row[‘rank_date’], ‘final_exp_point’ =>$row[$final_exp_point,’rank’] =>$row[‘rank’]));
}
[/php]
Step 3: Sort the array using array_multisort
[php]
/ Sort array on Level DESC and then EXP DESC
foreach ($final_array as $key => $row)
{
// Since Level is derived from Exp sorting only on Exp will serve the purpose
$fresh_array_var[$key] = $row[‘final_exp_point’];
}
array_multisort($fresh_array_var, SORT_DESC, $final_array);
[/php]
© Kamalika Guha Roy
Follow
Leave a Reply