• Home
  • Technology
    • Ebullientech
    • Entrepreneurship
    • Video Technology
    • Cloud Computing
    • Business Analysis
  • Healthcare
    • Pharmacology
  • Thoughts
    • Food
    • General
    • Stories
    • Places
    • Workplace
    • Social Issues
    • Reviews
  • Quotes
  • Ebullientech News and Events
  • About

Kamalika's Notebook

thinking nothing...............!!

You are here: Home / Kamalika / Technology / MySQL / Load Balanced Databases hosted in Cloud, MySQL ORDER BY and PHP Array Multi Sort!

Load Balanced Databases hosted in Cloud, MySQL ORDER BY and PHP Array Multi Sort!

February 10, 2013 By Kamalika Leave a Comment

Load-Balanced-Databases-hosted-in-Cloud,-MySQL-ORDER-BY-and-PHP-Array-Multi-Sort!

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

Filed Under: MySQL, PHP Tagged With: MySQL, PHP, PHP Array, PHP Array Multi Sort

« A Few Useful MySQL Commands / Queries for Amateur DBAs – Part 1
Top 10 Common MySQL Mistakes Made By PHP Developers »

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Connect with Facebook

Let’s connect…

  • Facebook
  • Instagram
  • LinkedIn
  • Twitter
  • YouTube

Hello There…

I'm Kamalika, a techpreneur & startup mentor, blogger, hobbyist photographer, Netflix & Kindle indulgent, food connoisseur, Starbucks aficionado and former Disney employee ...next

Looking for something?

Tags

All in One SEO Pack AWS Banaras Blogger Blogging Blogs Dasaswamedh Ghat Ebullientech Ebullientech Interactive LLP Facebook Facebook Developers father's day father's day wishes father's day wishes for dad fathers day love Fathers Day Special Fathers Day wishes from daughter Father’s day memories GeoTech GeoTech Informatics HipHop for PHP Kamalika Guha Roy Kashi Ganga LAMP MySQL Nabaneeta Guha Roy Online Marketing PayPal PayPal's suspension of Indian bank transactions PHP PHP & MySQL Saraswati Puja Search Engine Optimization SEO Startup The Ganges TypePad UCO HUT Varanasi Web 2.0 Web 2.0 Design Web Design Web Marketing WordPress WordPress.COM

Timeline

  • 2021 (3)
  • 2020 (7)
  • 2019 (4)
  • 2018 (1)
  • 2015 (6)
  • 2014 (13)
  • 2013 (9)
  • 2010 (7)
  • 2009 (2)

Topics

Copyright © 2021 · Kamalika Guha Roy, Powered by: Ebullientech