Then by Name: Ascending<\/li>\n<\/ol>\nI specify the orders in an array:<\/p>\n
\r\n$order = array('Identification' => 'ASC',\r\n\t\t\t 'Address' => 'DESC',\r\n\t\t\t 'Name' => 'ASC');\r\n<\/pre>\nAnd include the following function in my code:<\/p>\n
\r\n\/\/array_multiSort with natural sort\r\nfunction naturalSort2D(&$array){\r\n\r\n if (!function_exists(naturalSort2DCompare)){\r\n\tfunction naturalSort2DCompare($a, $b){\r\n\t\tglobal $order;\r\n\r\n\t\tforeach($order as $key => $value){\r\n\t\t\t\r\n\t\t\tif (!isset($a[$key])) continue;\r\n\r\n\t\t\tunset($compareResult);\r\n\r\n \/\/Case insensitive string comparisons using a \"natural order\" algorithm\r\n\t\t\t$compareResult = strnatcasecmp($a[$key], $b[$key]);\r\n\t\t\t\r\n\t\t\tif ($compareResult === 0) continue;\r\n\t\t\t\r\n\t\t\t$value = strtoupper(trim($value));\r\n\t\t\t\r\n\t\t\tif ($value === 'DESC'){\r\n\t\t\t\t$compareResult = $compareResult*-1;\r\n\t\t\t}\r\n\t\t\t\r\n\t\t\treturn $compareResult;\r\n\t\t}\r\n\t\t\r\n\t\treturn 0;\r\n\t}\r\n }\r\n\t\r\n \/\/Maintain index association\r\n \/\/Preserve array keys\r\n \/\/Use usort if you don't care about key index\r\n uasort($array, 'naturalSort2DCompare');\r\n\t\r\n return true;\r\n}\r\n<\/pre>\nNow I sort the array:<\/p>\n
\r\nnaturalSort2D($array);\r\n<\/pre>\nAnd I get my result like the following:<\/p>\n
\r\nArray\r\n(\r\n [4] => Array\r\n (\r\n [Name] => Paul\r\n [Identification] => 1\r\n [Address] => 10 Michigan Ave.\r\n )\r\n\r\n [3] => Array\r\n (\r\n [Name] => Tom\r\n [Identification] => 2\r\n [Address] => 10 Michigan Ave.\r\n )\r\n\r\n [1] => Array\r\n (\r\n [Name] => John\r\n [Identification] => 10\r\n [Address] => 100 Michigan Ave.\r\n )\r\n\r\n [2] => Array\r\n (\r\n [Name] => Peter\r\n [Identification] => 1000\r\n [Address] => 1 Michigan Ave.\r\n )\r\n\r\n)\r\n<\/pre>\n<\/div>\n
Performance<\/h2>\n
Before we talk about the performance issue, I like to talk about my database design first. In my application, the records are stored in a table. It is a multiple user system. Each user can access their own records only. Each user has about 3000 records, and we have 1000 users. Therefore we have about 3 millions of records in one table.<\/p>\n
Here is a data structure:<\/p>\n
\r\n#Information input by users\r\nName\r\nIdentification\r\nAddress\r\n\r\n#Information input by system\r\nID\r\nUserID\r\n<\/pre>\nSo the SQL quere is nothing more than something like:<\/p>\n
\r\nSelect * FROM `database`.`table` WHERE `UserID` = '1'\r\n<\/pre>\nPreviously, I let MySQL to handle the filter and sorting, i.e.,<\/p>\n
\r\nSelect * FROM `database`.`table` WHERE (`UserID` = '1') AND (`Address` LIKE `%Michigan%`) ORDER BY `Identification` ASC LIMIT 5, 10\r\n<\/pre>\nAfter moving to PHP, I just let MySQL to handle the filter,<\/p>\n
\r\nSelect * FROM `database`.`table` WHERE (`UserID` = '1') AND (`Address` LIKE `%Michigan%`)\r\n<\/pre>\nNotice that I will get all qualified records, could be few thousands. Then I let PHP to do the sorting:<\/p>\n
\r\n$order = array('Identification' => 'ASC');\r\nnaturalSort2D($array);\r\n<\/pre>\nNow I have a sorted array. Next I need to slice the records:<\/p>\n
\r\n$array = array_slice($array, 5, 10);\r\n<\/pre>\nSounds like lots of work huh? Now let’s talk about the performance impact. Guess what, I see performance improvement for about 15%<\/strong>!<\/p>\nIn the new approach, MySQL does lesser work. PHP does more work. Apparently, PHP works more effectively than MySQL. However, this may not true for all database model. Remember that in my situation, the system needs to handle at most few thousands records at a time? So I think PHP is doing better in this range. If your application needs to handle millions of records at a time, e.g., bookstore, then you better let MySQL to do the work.<\/p>\n
By the way, the new approach allows to sort the dynamic generated content, which is a big plus in my situation. That is something I cannot do in MySQL approach.<\/p>\n
Have fun.<\/p>\n
–Derrick<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
MySQL has a biggest missing feature: It does not sort naturally. This has been an opened request since 2003, and MySQL team has no plan to fix this in a Continue Reading →<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_hide_featured_image":false,"_jetpack_featured_image_duplicate_dismissed":0,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[94,95,141,6,69],"tags":[9,172,138],"class_list":["post-1948","post","type-post","status-publish","format-standard","hentry","category-computer-problems-computer-help-pc-support","category-howto","category-programming-problems","category-technology","category-troubleshooting-technology","tag-mysql","tag-php","tag-sort"],"jetpack_publicize_connections":[],"jetpack_shortlink":"https:\/\/wp.me\/p1sqzr-vq","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":2156,"url":"https:\/\/icesquare.com\/wordpress\/cant-connect-to-local-mysql-server-through-socket-varlibmysqlmysql-sock-2\/","url_meta":{"origin":1948,"position":0},"title":"Can’t connect to local MySQL server through socket ‘\/var\/lib\/mysql\/mysql.sock’ (2)","author":"Derrick","date":"December 21, 2011","format":false,"excerpt":"When I tried to connect to the MySQL server through PHP, I got the following error: Can't connect to local MySQL server through socket '\/var\/lib\/mysql\/mysql.sock' (2) To give you some background, here is my situation. My Fedora Linux is a 64-bit machine, and I have installed a 32-bit version of\u2026","rel":"","context":"In "Computer Problems"","block_context":{"text":"Computer Problems","link":"https:\/\/icesquare.com\/wordpress\/category\/technology\/computer-problems-computer-help-pc-support\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":41,"url":"https:\/\/icesquare.com\/wordpress\/installing-apache-php-mysql-and-memcached-on-os-x\/","url_meta":{"origin":1948,"position":1},"title":"Installing Apache, PHP, MySQL and MemCached on OS X","author":"Derrick","date":"May 19, 2009","format":false,"excerpt":"This is my note on how to to install Apache, PHP and MySQL on a an OS X box. Although some of these applications are come with the operating systems, it is always good to know how to upgrade them to the latest version. 1. Apple Developer Tools and Mac\u2026","rel":"","context":"In "Server Problems"","block_context":{"text":"Server Problems","link":"https:\/\/icesquare.com\/wordpress\/category\/sever-problem\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":603,"url":"https:\/\/icesquare.com\/wordpress\/weird-php-error-after-freebsd-port-upgrade\/","url_meta":{"origin":1948,"position":2},"title":"Weird PHP Error after FreeBSD port upgrade (PHP Warning: PHP Startup: Unable to load dynamic library)","author":"Derrick","date":"April 6, 2010","format":false,"excerpt":"Today I upgraded the port on my FreeBSD box using portmaster tools: portmaster -Dai After that, I found that the PHP gave the following weird error: php -v PHP Warning: PHP Startup: Unable to load dynamic library '\/usr\/local\/lib\/php\/20060613\/memcached.so' - \/usr\/local\/lib\/php\/20060613\/memcached.so: Undefined symbol \" php_session_create_id\" in Unknown on line 0 PHP\u2026","rel":"","context":"In "Computer Problems"","block_context":{"text":"Computer Problems","link":"https:\/\/icesquare.com\/wordpress\/category\/technology\/computer-problems-computer-help-pc-support\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":31,"url":"https:\/\/icesquare.com\/wordpress\/installing-apache-php-mysql-and-memcached-on-freebsd\/","url_meta":{"origin":1948,"position":3},"title":"Installing Apache, PHP, MySQL and MemCached on FreeBSD","author":"Derrick","date":"May 19, 2009","format":false,"excerpt":"This is my note on how to to install Apache, PHP, MySQL and MemCached on a FreeBSD 7 Box. 1. MySQL Go to the mysql52-server port directory by typing the command: cd \/usr\/ports\/databases\/mysql51-server Build the port by typing: make BUILD_OPTIMIZED=yes BUILD_STATIC=yes Using these two options will speed up the execution\u2026","rel":"","context":"In "FreeBSD"","block_context":{"text":"FreeBSD","link":"https:\/\/icesquare.com\/wordpress\/category\/sever-problem\/freebsd\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":110,"url":"https:\/\/icesquare.com\/wordpress\/tokyo-cabinet-is-31-times-faster-than-mysql\/","url_meta":{"origin":1948,"position":4},"title":"Tokyo Cabinet is 31 times faster than MySQL!!!","author":"Derrick","date":"May 23, 2009","format":false,"excerpt":"Today I was comparing the performance of Tokyo Cabinet, MySQL and Memcached using PHP. The result of Tokyo Cabinet is really impressive! (Note that these results are generated from my computer. If you perform your own benchmarks, then you might find different results due to different system configurations. In fact,\u2026","rel":"","context":"In "Programming Problems"","block_context":{"text":"Programming Problems","link":"https:\/\/icesquare.com\/wordpress\/category\/programming-problems\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1028,"url":"https:\/\/icesquare.com\/wordpress\/mysql-starting-mysql-error-the-server-quit-without-updating-pid-file\/","url_meta":{"origin":1948,"position":5},"title":"MySQL: Starting MySQL….. ERROR! The server quit without updating PID file","author":"Derrick","date":"November 12, 2010","format":false,"excerpt":"This step-by-step guide is mainly for FreeBSD, however the idea is the same for Linux. Every once a while, when I update my FreeBSD box, the system likes to shutdown my MySQL server. Therefore, I need to start it again after the update is done. Unfortunately, the upgrade process is\u2026","rel":"","context":"In "Computer Problems"","block_context":{"text":"Computer Problems","link":"https:\/\/icesquare.com\/wordpress\/category\/technology\/computer-problems-computer-help-pc-support\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/icesquare.com\/wordpress\/wp-json\/wp\/v2\/posts\/1948","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/icesquare.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/icesquare.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/icesquare.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/icesquare.com\/wordpress\/wp-json\/wp\/v2\/comments?post=1948"}],"version-history":[{"count":40,"href":"https:\/\/icesquare.com\/wordpress\/wp-json\/wp\/v2\/posts\/1948\/revisions"}],"predecessor-version":[{"id":2858,"href":"https:\/\/icesquare.com\/wordpress\/wp-json\/wp\/v2\/posts\/1948\/revisions\/2858"}],"wp:attachment":[{"href":"https:\/\/icesquare.com\/wordpress\/wp-json\/wp\/v2\/media?parent=1948"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/icesquare.com\/wordpress\/wp-json\/wp\/v2\/categories?post=1948"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/icesquare.com\/wordpress\/wp-json\/wp\/v2\/tags?post=1948"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"\"](\"\/wordpress\/wp-content\/uploads\/2011\/12\/data-sorting-lego-300x225.jpg\" "\\"Sort\\"")
<\/p>\n