A step-by-step look at MySQL returns results in a non-deterministic order.
Whenever we want a query's results sorted, you may think of using the clause "order by." You are correct: most of the time, MySQL will return the results in expected order with "order by."
Be careful, though. Sometimes MySQL may return results in the non-deterministic order even with "order by."
Indeed, if a query is ordered by a non-unique column, it may return results in an unexpected order. I tested the below example on MySQL 5.1.73, 5.5.54 and 5.6.19 and got the same result. However, when I applied the same example on MySQL 5.7.17, it returned the results in an unexpected order differently.
Follow me step-by-step and see how MySQL returns results in a non-deterministic order. Step 1-4 is for MySQL 5.1.73, 5.5.54 and 5.6.19, Step 5 is for MySQL 5.7.17. After the example, I will explain the reason behind this output.
Step 1. Create the table as below:
CREATE TABLE `user_rankings` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sort_order` int(11) NOT NULL,
`equals_role` varchar(40) NOT NULL DEFAULT '',
`equals_user_id` int(11) NOT NULL,
`metric_posts` int(11) NOT NULL DEFAULT '0',
`metric_pageviews` int(11) NOT NULL DEFAULT '0',
`metric_logins` int(11) NOT NULL DEFAULT '0',
`metric_minutes_online` int(11) NOT NULL DEFAULT '0',
`average_message_rating` int(11) NOT NULL DEFAULT '0',
`registration_age` int(11) NOT NULL DEFAULT '0',
`metric_tags` int(11) NOT NULL DEFAULT '0',
`rank_formula_enabled` tinyint(1) DEFAULT NULL,
`rank_formula` varchar(5000) DEFAULT NULL,
`rank_name` varchar(80) NOT NULL,
`rank_bold` tinyint(1) NOT NULL DEFAULT '0',
`rank_color` varchar(6) NOT NULL DEFAULT '',
`rank_icon` varchar(255) NOT NULL DEFAULT '',
`rank_icon_right` varchar(255) NOT NULL DEFAULT '',
`rank_icon_thread` varchar(255) NOT NULL DEFAULT '',
`grant_roles` varchar(4100) NOT NULL DEFAULT '',
`remove_roles` varchar(4100) NOT NULL DEFAULT '',
`kudos_weight` int(11) DEFAULT NULL,
`deleted` tinyint(1) NOT NULL DEFAULT '0',
`row_version` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `IDX_USER_RANKINGS_ROW_VERSION` (`row_version`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8;
Step 2. Insert into the table 31 rows of data as below:
INSERT INTO `user_rankings` VALUES (1,0,'Administrator,Ipsos Admin',-1,0,0,0,0,0,0,0,1,'hasRole(\"Administrator\") || hasRole(\"Ipsos Admin\") || hasRole(\"Acat_Moderator\") || hasRole(\"Bcat_Moderator\") || hasRole(\"Ccat_Moderator\") || hasRole(\"Dcat_Moderator\") || hasRole(\"Ecat_Moderator\") || hasRole(\"Fcat_Moderator\") || hasRole(\"Gcat_Moderator\") || hasRole(\"Hcat_Moderator\") || hasRole(\"Icat_Moderator\") || hasRole(\"Jcat_Moderator\") || hasRole(\"Kcat_Admin\") || hasRole(\"Lcat_Admin\") || hasRole(\"Mcat_Admin\") || hasRole(\"Ncat_Admin\") || hasRole(\"Ocat_Admin\") || hasRole(\"Pcat_Admin\") || hasRole(\"Qcat_Admin\") || hasRole(\"Rcat_Admin\") || hasRole(\"Scat_Admin\") || hasRole(\"Tcat_Admin\") || hasRole(\"Ucat_Admin\") || hasRole(\"Vcat_Admin\") || hasRole(\"Wcat_Admin\") || hasRole(\"Xcat_Admin\") || hasRole(\"Ycat_Admin\") || hasRole(\"Zcat_Admin\") || hasRole(\"Dcat_Moderator\") || hasRole(\"Ccat_Moderator\") || hasRole(\"Bcat_Moderator\") || hasRole(\"Ecat_Moderator\")','Community Manager',1,'009966','/i/rank_icons/admin.gif','','','','',7,0,'2016-10-25 00:16:34'), (2,1,'Moderator',-1,0,0,0,0,0,0,0,0,'','Moderator',1,'009966','/i/rank_icons/moderator.gif','','','','',7,0,'2016-07-07 01:48:03'), (3,2,'Member',-1,0,0,0,0,0,0,0,0,'hasRole(\"Member\") || hasRole(\"Acat_Member\") || hasRole(\"Bcat_Member\") || hasRole(\"Ccat_Member\") || hasRole(\"Dcat_Member\") || hasRole(\"Ecat_Member\") || hasRole(\"Fcat_Member\") || hasRole(\"Gcat_Member\") || hasRole(\"Hcat_Member\") || hasRole(\"Icat_Member\") || hasRole(\"Jcat_Member\") || hasRole(\"Kcat_Member\") || hasRole(\"Lcat_Member\") || hasRole(\"Mcat_Member\") || hasRole(\"Ncat_Member\") || hasRole(\"Ocat_Member\") || hasRole(\"Pcat_Member\") || hasRole(\"Qcat_Member\") || hasRole(\"Rcat_Member\") || hasRole(\"Scat_Member\") || hasRole(\"Tcat_Member\") || hasRole(\"Ucat_Member\") || hasRole(\"Vcat_Member\") || hasRole(\"Wcat_Member\") || hasRole(\"Xcat_Member\") || hasRole(\"Ycat_Member\") || hasRole(\"Zcat_Member\")','Member',0,'666666','','','','','',-1,0,'2016-09-27 07:37:42'),(4,3,'Client',-1,0,0,0,0,0,0,0,1,'hasRole(\"Client\") || hasRole(\"Acat_Client\") || hasRole(\"Bcat_Client\") || hasRole(\"Ccat_Client\") || hasRole(\"Dcat_Client\") || hasRole(\"Ecat_Client\") || hasRole(\"Fcat_Client\") || hasRole(\"Gcat_Client\") || hasRole(\"Hcat_Client\") || hasRole(\"Icat_Client\") || hasRole(\"Jcat_Client\") || hasRole(\"Kcat_Client\") || hasRole(\"Lcat_Client\") || hasRole(\"Mcat_Client\") || hasRole(\"Ncat_Client\") || hasRole(\"Ocat_Client\") || hasRole(\"Pcat_Client\") || hasRole(\"Qcat_Client\") || hasRole(\"Rcat_Client\") || hasRole(\"Scat_Client\") || hasRole(\"Tcat_Client\") || hasRole(\"Ucat_Client\") || hasRole(\"Vcat_Client\") || hasRole(\"Wcat_Client\") || hasRole(\"Xcat_Client\") || hasRole(\"Ycat_Client\") || hasRole(\"Zcat_Client\")','VIP',1,'000000','/html/rank_icons/VIP.png','','','','',3,0,'2016-09-27 07:33:14'),(5,4,'Administrator',-1,0,0,0,0,0,0,0,1,'(overall_posts >= 805) && (minutes_online >= 6125) && (net_kudos_weight_received >= 1225) && (net_kudos_weight_given >= 350) && hasRole(\"Administrator\")','Super Star',1,'fd4403','/html/rank_icons/rank_superstar.png','','','','',3,0,'2016-07-07 01:48:03'),(6,5,'Administrator',-1,0,0,0,0,0,0,0,1,'(overall_posts >= 630) && (minutes_online >= 4775) && (net_kudos_weight_received >= 1000) && (net_kudos_weight_given >= 240) && hasRole(\"Administrator\")','Rising Star',1,'fb8717','/html/rank_icons/rank_risingstar.png','','','','',3,0,'2016-07-07 01:48:03'),(7,6,'',-1,0,0,0,0,0,0,0,1,'(overall_posts >= 480) && (minutes_online >= 3650) && (net_kudos_weight_received >= 800) && (net_kudos_weight_given >= 160)','Star',1,'fcb223','/html/rank_icons/rank_star.png','','','','',3,0,'2016-07-07 01:48:03'), (8,7,'',-1,0,0,0,0,0,0,0,1,'(overall_posts >= 355) && (minutes_online >= 2750) && (net_kudos_weight_received >= 625) && (net_kudos_weight_given >= 100)','Honored Member',1,'663399','/html/rank_icons/rank_honoredmember.gif','','','','',2,1,'2016-07-25 19:20:02'), (9,8,'',-1,0,0,0,0,0,0,0,1,'(overall_posts >= 280) && (minutes_online >= 2050) && (net_kudos_weight_received >= 475) && (net_kudos_weight_given >= 60)','Esteemed Member',1,'9933CC','/html/rank_icons/rank_esteemedmember.gif','','','','',2,1,'2016-07-25 19:19:43'),(10,9,'',-1,0,0,0,0,0,0,0,1,'(overall_posts >= 215) && (minutes_online >= 1375) && (net_kudos_weight_received >= 350) && (net_kudos_weight_given >= 30)','Respected Member',1,'CC00CC','/html/rank_icons/rank_respectedmember.gif','','','','',2,1,'2016-07-25 19:19:27'),(11,10,'',-1,0,0,0,0,0,0,0,1,'(overall_posts >= 150) && (minutes_online >= 775) && (net_kudos_weight_received >= 250) && (net_kudos_weight_given >= 5)','Trusted Member',1,'FF66CC','/html/rank_icons/rank_trustedmember.gif','','','','',2,1,'2016-07-25 19:19:12'),(12,11,'',-1,0,0,0,0,0,0,0,1,'(overall_posts >= 90) && (logins >= 10) && (minutes_online >= 225)','Super Contributor',0,'0f68d6','/html/rank_icons/rank_supercontributor.png','','','','',1,1,'2016-07-25 19:18:52'),(13,12,'',-1,0,0,0,0,0,0,0,1,'(overall_posts >= 45) && (logins >= 7) && (minutes_online >= 135)','Regular Contributor',0,'02b1cb','/html/rank_icons/rank_Regularcontributor.png','','','','',1,1,'2016-07-25 19:18:43'),(14,13,'',-1,0,0,0,0,0,0,0,1,'(overall_posts >= 10) && (logins >= 5) && (minutes_online >= 75) && (net_kudos_weight_received >= 15)','Liked Contributor',0,'02cb28','/html/rank_icons/rank_kudoedcontributor.png','','','','',1,1,'2016-07-25 19:18:07'),(15,14,'',-1,0,0,0,0,0,0,0,1,'(overall_posts >= 10) && (logins >= 5) && (minutes_online >= 75)','Frequent Contributor',0,'66CC00','','','','','',1,1,'2016-07-25 19:17:44'),(16,15,'',-1,0,0,0,0,0,0,0,1,'(overall_posts >= 4) && (logins >= 2) && (minutes_online >= 20)','Occasional Contributor',0,'000099','','','','','',1,1,'2016-07-25 19:17:29'), (17,16,'',-1,0,0,0,0,0,0,0,1,'(overall_posts >= 1)','New Contributor',0,'3399FF','','','','','',1,1,'2016-07-25 19:17:18'),(18,17,'',-1,0,0,0,0,0,0,0,1,'(logins >= 8)','Regular Visitor',0,'000000','','','','','',-1,1,'2016-07-25 19:17:05'), (19,18,'',-1,0,0,0,0,0,0,0,1,'(logins >= 6)','Frequent Visitor',0,'000000','','','','','',-1,1,'2016-07-25 19:16:54'),(20,7,'',-1,0,0,0,0,0,0,0,1,'(logins >= 4)','Visitor',0,'666666','','','','','',-1,0,'2016-07-25 19:20:02'), (21,8,'',-1,0,0,0,0,0,0,0,0,'','New Member',0,'666666','','','','','',-1,0,'2016-07-25 19:20:02'),(22,21,'Staff',-1,0,0,0,0,0,0,0,0,'','Staff',0,'0000CC','','','','','',-1,1,'2016-07-25 19:16:33'),(23,22,'',-1,8000,0,0,0,0,300000,0,1,'(((posts - deleted_posts) + net_idea_threads) >= 8000) && (registrationAge >= 300000) && ((net_kudos_weight_received +(net_accepted_solutions*10)) >= 1500)','Honored Contributor',1,'660099','','','','','',-1,1,'2016-07-25 19:16:17'),(24,23,'',-1,5000,0,0,0,0,250000,0,1,'(((posts - deleted_posts) + net_idea_threads) >= 5000) && (registrationAge >= 250000) && ((net_kudos_weight_received + (net_accepted_solutions*10)) >= 800)','Esteemed Contributor',1,'660099','','','','','',-1,1,'2016-07-25 19:16:06'),(25,24,'',-1,2000,0,0,0,0,200000,0,1,'(((posts - deleted_posts) + net_idea_threads) >= 2000) && (registrationAge >= 200000) && ((net_kudos_weight_received + (net_accepted_solutions*10)) >= 400)','Respected Contributor',1,'660099','','','','','',-1,1,'2016-07-25 19:15:55'),(26,25,'',-1,1000,0,0,0,0,165000,0,1,'(((posts - deleted_posts) + net_idea_threads) >= 1000) && (registrationAge >= 165000) && ((net_kudos_weight_received + (net_accepted_solutions*10)) >= 200)','Trusted Contributor',1,'660099','','','','','',-1,1,'2016-07-25 19:15:35'),(27,26,'',-1,500,0,0,0,0,125000,0,1,'(((posts - deleted_posts) + net_idea_threads) >= 500) && (registrationAge >= 125000) && ((net_kudos_weight_received + (net_accepted_solutions*10)) >= 75)','Valued Contributor',1,'660099','','','','','',-1,1,'2016-07-25 19:15:21'),(28,27,'',-1,20,0,0,0,0,10080,0,0,'','Contributor',0,'3300CC','','','','','',-1,1,'2016-07-25 19:15:09'),(29,28,'',-1,0,0,18,0,0,129600,0,1,'(logins >=18) && (registrationAge >= 43200) && (message_views >= 70)','Senior Member',0,'000000','','','','','',-1,1,'2016-07-25 19:14:55'),(30,29,'',-1,0,0,16,0,0,86400,0,1,'(logins >=16) && (registrationAge >= 43200) && (message_views >= 50)','Established Member',0,'000000','','','','','',-1,1,'2016-07-25 19:14:46'),(31,30,'',-1,0,0,2,0,0,2880,0,0,'','Occasional Visitor',0,'000000','','','','','',-1,1,'2016-07-25 19:14:28');
Step 3. Execute the queries order by the column sort_order which is not unique
mysql# SELECT id, rank_name ,sort_order FROM user_rankings where sort_order in (7,8) ORDER BY sort_order;
+----+-----------------+------------+
| id | rank_name | sort_order |
+----+-----------------+------------+
| 8 | Honored Member | 7 |
| 20 | Visitor | 7 |
| 9 | Esteemed Member | 8 |
| 21 | New Member | 8 |
+----+-----------------+------------+
4 rows in set (0.00 sec)
mysql# select id,rank_name,sort_order from user_rankings order by sort_order;
+----+------------------------+------------+
| id | rank_name | sort_order |
+----+------------------------+------------+
| 1 | Community Manager | 0 |
| 2 | Moderator | 1 |
| 3 | Member | 2 |
| 4 | VIP | 3 |
| 5 | Super Star | 4 |
| 6 | Rising Star | 5 |
| 7 | Star | 6 |
| 20 | Visitor | 7 |
| 8 | Honored Member | 7 |
| 21 | New Member | 8 |
| 9 | Esteemed Member | 8 |
| 10 | Respected Member | 9 |
| 11 | Trusted Member | 10 |
| 12 | Super Contributor | 11 |
| 13 | Regular Contributor | 12 |
| 14 | Liked Contributor | 13 |
| 15 | Frequent Contributor | 14 |
| 16 | Occasional Contributor | 15 |
| 17 | New Contributor | 16 |
| 18 | Regular Visitor | 17 |
| 19 | Frequent Visitor | 18 |
| 22 | Staff | 21 |
| 23 | Honored Contributor | 22 |
| 24 | Esteemed Contributor | 23 |
| 25 | Respected Contributor | 24 |
| 26 | Trusted Contributor | 25 |
| 27 | Valued Contributor | 26 |
| 28 | Contributor | 27 |
| 29 | Senior Member | 28 |
| 30 | Established Member | 29 |
| 31 | Occasional Visitor | 30 |
+----+------------------------+------------+
31 rows in set (0.00 sec)
mysql# select * from user_rankings order by sort_order;
+----+------------+---------------------------+----------------+--------------+------------------+---------------+-----------------------+------------------------+------------------+-------------+----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+-----------+------------+----------------------------------------------+-----------------+------------------+-------------+--------------+--------------+---------+---------------------+
| id | sort_order | equals_role | equals_user_id | metric_posts | metric_pageviews | metric_logins | metric_minutes_online | average_message_rating | registration_age | metric_tags | rank_formula_enabled | rank_formula | rank_name | rank_bold | rank_color | rank_icon | rank_icon_right | rank_icon_thread | grant_roles | remove_roles | kudos_weight | deleted | row_version |
+----+------------+---------------------------+----------------+--------------+------------------+---------------+-----------------------+------------------------+------------------+-------------+----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+-----------+------------+----------------------------------------------+-----------------+------------------+-------------+--------------+--------------+---------+---------------------+
| 1 | 0 | Administrator,Ipsos Admin | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | hasRole("Administrator") || hasRole("Ipsos Admin") || hasRole("Acat_Moderator") || hasRole("Bcat_Moderator") || hasRole("Ccat_Moderator") || hasRole("Dcat_Moderator") || hasRole("Ecat_Moderator") || hasRole("Fcat_Moderator") || hasRole("Gcat_Moderator") || hasRole("Hcat_Moderator") || hasRole("Icat_Moderator") || hasRole("Jcat_Moderator") || hasRole("Kcat_Admin") || hasRole("Lcat_Admin") || hasRole("Mcat_Admin") || hasRole("Ncat_Admin") || hasRole("Ocat_Admin") || hasRole("Pcat_Admin") || hasRole("Qcat_Admin") || hasRole("Rcat_Admin") || hasRole("Scat_Admin") || hasRole("Tcat_Admin") || hasRole("Ucat_Admin") || hasRole("Vcat_Admin") || hasRole("Wcat_Admin") || hasRole("Xcat_Admin") || hasRole("Ycat_Admin") || hasRole("Zcat_Admin") || hasRole("Dcat_Moderator") || hasRole("Ccat_Moderator") || hasRole("Bcat_Moderator") || hasRole("Ecat_Moderator") | Community Manager | 1 | 009966 | /i/rank_icons/admin.gif | | | | | 7 | 0 | 2016-10-24 17:16:34 |
| 2 | 1 | Moderator | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | Moderator | 1 | 009966 | /i/rank_icons/moderator.gif | | | | | 7 | 0 | 2016-07-06 18:48:03 |
| 3 | 2 | Member | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | hasRole("Member") || hasRole("Acat_Member") || hasRole("Bcat_Member") || hasRole("Ccat_Member") || hasRole("Dcat_Member") || hasRole("Ecat_Member") || hasRole("Fcat_Member") || hasRole("Gcat_Member") || hasRole("Hcat_Member") || hasRole("Icat_Member") || hasRole("Jcat_Member") || hasRole("Kcat_Member") || hasRole("Lcat_Member") || hasRole("Mcat_Member") || hasRole("Ncat_Member") || hasRole("Ocat_Member") || hasRole("Pcat_Member") || hasRole("Qcat_Member") || hasRole("Rcat_Member") || hasRole("Scat_Member") || hasRole("Tcat_Member") || hasRole("Ucat_Member") || hasRole("Vcat_Member") || hasRole("Wcat_Member") || hasRole("Xcat_Member") || hasRole("Ycat_Member") || hasRole("Zcat_Member") | Member | 0 | 666666 | | | | | | -1 | 0 | 2016-09-27 00:37:42 |
| 4 | 3 | Client | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | hasRole("Client") || hasRole("Acat_Client") || hasRole("Bcat_Client") || hasRole("Ccat_Client") || hasRole("Dcat_Client") || hasRole("Ecat_Client") || hasRole("Fcat_Client") || hasRole("Gcat_Client") || hasRole("Hcat_Client") || hasRole("Icat_Client") || hasRole("Jcat_Client") || hasRole("Kcat_Client") || hasRole("Lcat_Client") || hasRole("Mcat_Client") || hasRole("Ncat_Client") || hasRole("Ocat_Client") || hasRole("Pcat_Client") || hasRole("Qcat_Client") || hasRole("Rcat_Client") || hasRole("Scat_Client") || hasRole("Tcat_Client") || hasRole("Ucat_Client") || hasRole("Vcat_Client") || hasRole("Wcat_Client") || hasRole("Xcat_Client") || hasRole("Ycat_Client") || hasRole("Zcat_Client") | VIP | 1 | 000000 | /html/rank_icons/VIP.png | | | | | 3 | 0 | 2016-09-27 00:33:14 |
| 5 | 4 | Administrator | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | (overall_posts >= 805) && (minutes_online >= 6125) && (net_kudos_weight_received >= 1225) && (net_kudos_weight_given >= 350) && hasRole("Administrator") | Super Star | 1 | fd4403 | /html/rank_icons/rank_superstar.png | | | | | 3 | 0 | 2016-07-06 18:48:03 |
| 6 | 5 | Administrator | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | (overall_posts >= 630) && (minutes_online >= 4775) && (net_kudos_weight_received >= 1000) && (net_kudos_weight_given >= 240) && hasRole("Administrator") | Rising Star | 1 | fb8717 | /html/rank_icons/rank_risingstar.png | | | | | 3 | 0 | 2016-07-06 18:48:03 |
| 7 | 6 | | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | (overall_posts >= 480) && (minutes_online >= 3650) && (net_kudos_weight_received >= 800) && (net_kudos_weight_given >= 160) | Star | 1 | fcb223 | /html/rank_icons/rank_star.png | | | | | 3 | 0 | 2016-07-06 18:48:03 |
| 8 | 7 | | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | (overall_posts >= 355) && (minutes_online >= 2750) && (net_kudos_weight_received >= 625) && (net_kudos_weight_given >= 100) | Honored Member | 1 | 663399 | /html/rank_icons/rank_honoredmember.gif | | | | | 2 | 1 | 2016-07-25 12:20:02 |
| 20 | 7 | | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | (logins >= 4) | Visitor | 0 | 666666 | | | | | | -1 | 0 | 2016-07-25 12:20:02 |
| 9 | 8 | | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | (overall_posts >= 280) && (minutes_online >= 2050) && (net_kudos_weight_received >= 475) && (net_kudos_weight_given >= 60) | Esteemed Member | 1 | 9933CC | /html/rank_icons/rank_esteemedmember.gif | | | | | 2 | 1 | 2016-07-25 12:19:43 |
| 21 | 8 | | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | New Member | 0 | 666666 | | | | | | -1 | 0 | 2016-07-25 12:20:02 |
| 10 | 9 | | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | (overall_posts >= 215) && (minutes_online >= 1375) && (net_kudos_weight_received >= 350) && (net_kudos_weight_given >= 30) | Respected Member | 1 | CC00CC | /html/rank_icons/rank_respectedmember.gif | | | | | 2 | 1 | 2016-07-25 12:19:27 |
| 11 | 10 | | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | (overall_posts >= 150) && (minutes_online >= 775) && (net_kudos_weight_received >= 250) && (net_kudos_weight_given >= 5) | Trusted Member | 1 | FF66CC | /html/rank_icons/rank_trustedmember.gif | | | | | 2 | 1 | 2016-07-25 12:19:12 |
| 12 | 11 | | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | (overall_posts >= 90) && (logins >= 10) && (minutes_online >= 225) | Super Contributor | 0 | 0f68d6 | /html/rank_icons/rank_supercontributor.png | | | | | 1 | 1 | 2016-07-25 12:18:52 |
| 13 | 12 | | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | (overall_posts >= 45) && (logins >= 7) && (minutes_online >= 135) | Regular Contributor | 0 | 02b1cb | /html/rank_icons/rank_Regularcontributor.png | | | | | 1 | 1 | 2016-07-25 12:18:43 |
| 14 | 13 | | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | (overall_posts >= 10) && (logins >= 5) && (minutes_online >= 75) && (net_kudos_weight_received >= 15) | Liked Contributor | 0 | 02cb28 | /html/rank_icons/rank_kudoedcontributor.png | | | | | 1 | 1 | 2016-07-25 12:18:07 |
| 15 | 14 | | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | (overall_posts >= 10) && (logins >= 5) && (minutes_online >= 75) | Frequent Contributor | 0 | 66CC00 | | | | | | 1 | 1 | 2016-07-25 12:17:44 |
| 16 | 15 | | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | (overall_posts >= 4) && (logins >= 2) && (minutes_online >= 20) | Occasional Contributor | 0 | 000099 | | | | | | 1 | 1 | 2016-07-25 12:17:29 |
| 17 | 16 | | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | (overall_posts >= 1) | New Contributor | 0 | 3399FF | | | | | | 1 | 1 | 2016-07-25 12:17:18 |
| 18 | 17 | | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | (logins >= 8) | Regular Visitor | 0 | 000000 | | | | | | -1 | 1 | 2016-07-25 12:17:05 |
| 19 | 18 | | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | (logins >= 6) | Frequent Visitor | 0 | 000000 | | | | | | -1 | 1 | 2016-07-25 12:16:54 |
| 22 | 21 | Staff | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | Staff | 0 | 0000CC | | | | | | -1 | 1 | 2016-07-25 12:16:33 |
| 23 | 22 | | -1 | 8000 | 0 | 0 | 0 | 0 | 300000 | 0 | 1 | (((posts - deleted_posts) + net_idea_threads) >= 8000) && (registrationAge >= 300000) && ((net_kudos_weight_received +(net_accepted_solutions*10)) >= 1500) | Honored Contributor | 1 | 660099 | | | | | | -1 | 1 | 2016-07-25 12:16:17 |
| 24 | 23 | | -1 | 5000 | 0 | 0 | 0 | 0 | 250000 | 0 | 1 | (((posts - deleted_posts) + net_idea_threads) >= 5000) && (registrationAge >= 250000) && ((net_kudos_weight_received + (net_accepted_solutions*10)) >= 800) | Esteemed Contributor | 1 | 660099 | | | | | | -1 | 1 | 2016-07-25 12:16:06 |
| 25 | 24 | | -1 | 2000 | 0 | 0 | 0 | 0 | 200000 | 0 | 1 | (((posts - deleted_posts) + net_idea_threads) >= 2000) && (registrationAge >= 200000) && ((net_kudos_weight_received + (net_accepted_solutions*10)) >= 400) | Respected Contributor | 1 | 660099 | | | | | | -1 | 1 | 2016-07-25 12:15:55 |
| 26 | 25 | | -1 | 1000 | 0 | 0 | 0 | 0 | 165000 | 0 | 1 | (((posts - deleted_posts) + net_idea_threads) >= 1000) && (registrationAge >= 165000) && ((net_kudos_weight_received + (net_accepted_solutions*10)) >= 200) | Trusted Contributor | 1 | 660099 | | | | | | -1 | 1 | 2016-07-25 12:15:35 |
| 27 | 26 | | -1 | 500 | 0 | 0 | 0 | 0 | 125000 | 0 | 1 | (((posts - deleted_posts) + net_idea_threads) >= 500) && (registrationAge >= 125000) && ((net_kudos_weight_received + (net_accepted_solutions*10)) >= 75) | Valued Contributor | 1 | 660099 | | | | | | -1 | 1 | 2016-07-25 12:15:21 |
| 28 | 27 | | -1 | 20 | 0 | 0 | 0 | 0 | 10080 | 0 | 0 | | Contributor | 0 | 3300CC | | | | | | -1 | 1 | 2016-07-25 12:15:09 |
| 29 | 28 | | -1 | 0 | 0 | 18 | 0 | 0 | 129600 | 0 | 1 | (logins >=18) && (registrationAge >= 43200) && (message_views >= 70) | Senior Member | 0 | 000000 | | | | | | -1 | 1 | 2016-07-25 12:14:55 |
| 30 | 29 | | -1 | 0 | 0 | 16 | 0 | 0 | 86400 | 0 | 1 | (logins >=16) && (registrationAge >= 43200) && (message_views >= 50) | Established Member | 0 | 000000 | | | | | | -1 | 1 | 2016-07-25 12:14:46 |
| 31 | 30 | | -1 | 0 | 0 | 2 | 0 | 0 | 2880 | 0 | 0 | | Occasional Visitor | 0 | 000000 | | | | | | -1 | 1 | 2016-07-25 12:14:28 |
+----+------------+---------------------------+----------------+--------------+------------------+---------------+-----------------------+------------------------+------------------+-------------+----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+-----------+------------+----------------------------------------------+-----------------+------------------+-------------+--------------+--------------+---------+---------------------+
31 rows in set (0.00 sec)
Step 4. After deleting a few rows of the table, executed the queries again
mysql# delete from user_rankings where id>21;
Query OK, 10 rows affected (0.01 sec)
mysql# select id,rank_name,sort_order from user_rankings order by sort_order;
+----+------------------------+------------+
| id | rank_name | sort_order |
+----+------------------------+------------+
| 1 | Community Manager | 0 |
| 2 | Moderator | 1 |
| 3 | Member | 2 |
| 4 | VIP | 3 |
| 5 | Super Star | 4 |
| 6 | Rising Star | 5 |
| 7 | Star | 6 |
| 8 | Honored Member | 7 |
| 20 | Visitor | 7 |
| 21 | New Member | 8 |
| 9 | Esteemed Member | 8 |
| 10 | Respected Member | 9 |
| 11 | Trusted Member | 10 |
| 12 | Super Contributor | 11 |
| 13 | Regular Contributor | 12 |
| 14 | Liked Contributor | 13 |
| 15 | Frequent Contributor | 14 |
| 16 | Occasional Contributor | 15 |
| 17 | New Contributor | 16 |
| 18 | Regular Visitor | 17 |
| 19 | Frequent Visitor | 18 |
+----+------------------------+------------+
21 rows in set (0.00 sec)
mysql# delete from user_rankings where sort_order>9;
Query OK, 9 rows affected (0.04 sec)
mysql# select id,rank_name,sort_order from user_rankings order by sort_order;
+----+-------------------+------------+
| id | rank_name | sort_order |
+----+-------------------+------------+
| 1 | Community Manager | 0 |
| 2 | Moderator | 1 |
| 3 | Member | 2 |
| 4 | VIP | 3 |
| 5 | Super Star | 4 |
| 6 | Rising Star | 5 |
| 7 | Star | 6 |
| 8 | Honored Member | 7 |
| 20 | Visitor | 7 |
| 9 | Esteemed Member | 8 |
| 21 | New Member | 8 |
| 10 | Respected Member | 9 |
+----+-------------------+------------+
12 rows in set (0.00 sec)
Step 5. On MySQL 5.7.17, returned the results in unexpected order differently
mysql# SELECT id,rank_name,sort_order FROM user_rankings where sort_order in('7','8') ORDER BY sort_order;
+----+-----------------+------------+
| id | rank_name | sort_order |
+----+-----------------+------------+
| 8 | Honored Member | 7 |
| 20 | Visitor | 7 |
| 9 | Esteemed Member | 8 |
| 21 | New Member | 8 |
+----+-----------------+------------+
4 rows in set (0.00 sec)
mysql# select * from user_rankings ORDER BY sort_order;
...
| 8 | 7 | | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | (overall_posts >= 355) && (minutes_online >= 2750) && (net_kudos_weight_received >= 625) && (net_kudos_weight_given >= 100) | Honored Member | 1 | 663399 | /html/rank_icons/rank_honoredmember.gif | | | | | 2 | 1 | 2016-07-25 12:20:02 |
| 20 | 7 | | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | (logins >= 4) | Visitor | 0 | 666666 | | | | | | -1 | 0 | 2016-07-25 12:20:02 |
| 9 | 8 | | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | (overall_posts >= 280) && (minutes_online >= 2050) && (net_kudos_weight_received >= 475) && (net_kudos_weight_given >= 60) | Esteemed Member | 1 | 9933CC | /html/rank_icons/rank_esteemedmember.gif | | | | | 2 | 1 | 2016-07-25 12:19:43 |
| 21 | 8 | | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | New Member | 0 | 666666 | | | | | | -1 | 0 | 2016-07-25 12:20:02 |
...
31 rows in set (0.03 sec)
mysql# SELECT id,rank_name,sort_order FROM user_rankings ORDER BY sort_order;
...
| 20 | Visitor | 7 |
| 8 | Honored Member | 7 |
| 9 | Esteemed Member | 8 |
| 21 | New Member | 8 |
...
31 rows in set (0.03 sec)
mysql# delete from user_rankings where id>21;
Query OK, 10 rows affected (0.18 sec)
mysql# SELECT id,rank_name,sort_order FROM user_rankings ORDER BY sort_order;
...
| 8 | Honored Member | 7 |
| 20 | Visitor | 7 |
| 21 | New Member | 8 |
| 9 | Esteemed Member | 8 |
...
21 rows in set (0.00 sec)
mysql# delete from user_rankings where sort_order>9;
Query OK, 9 rows affected (0.05 sec)
mysql# SELECT id,rank_name,sort_order FROM user_rankings ORDER BY sort_order;
...
| 8 | Honored Member | 7 |
| 20 | Visitor | 7 |
| 9 | Esteemed Member | 8 |
| 21 | New Member | 8 |
...
12 rows in set (0.00 sec)
Some relevant variables:
mysql# show variables like '%sort%';
+--------------------------------+---------------------+
| Variable_name | Value |
+--------------------------------+---------------------+
| innodb_disable_sort_file_cache | OFF |
| innodb_ft_sort_pll_degree | 2 |
| innodb_sort_buffer_size | 1048576 |
| max_length_for_sort_data | 1024 |
| max_sort_length | 1024 |
| myisam_max_sort_file_size | 9223372036853727232 |
| myisam_sort_buffer_size | 8388608 |
| sort_buffer_size | 262144 |
+--------------------------------+---------------------+
8 rows in set (0.00 sec)
Please note, there are 4 tuples out of 31 rows with a duplicated sort_order value:
- two having sort_order=7
- the other two having sort_order=8
In step 3, the query with WHERE sort_order in (7,8) ORDER BY sort_order returned tuples in the ‘correct’ order:
- the row with id=8 in front of the row with id=20
- the row with id=9 in front of the row with id=21
When the query selected all of the columns and all the tuples with ORDER BY sort_order, it returned in the ‘correct’ order again:
- the row with id=8 in front of the row with id=20
- the row with id=9 in front of the row with id=21
But when the query selected part of the columns and all the tuples with ORDER BY sort_order, it returned in the ‘wrong’ order:
- the row with id=20 in front of the row with id=8
- the row with id=21 in front of the row with id=9
Additionally, in step 4 after deleting the last 10 rows with id>21, we can see half of the 4 tuples returned in ‘correct’ order:
- the row with id=8 in front of the row with id=20
The other half still returned in ‘wrong’ order:
- the row with id=21 in front of the row with id=9
After deleting 9 more rows with sort_order>9, now we can see all the 4 tuples returned in the ‘correct’ order:
- the row with id=8 in front of the row with id=20
- the row with id=9 in front of the row with id=21
When I applied the same table with the same data on MySQL 5.7.17, it behaved differently as below:
When the query selected part of the columns and all the tuples with ORDER BY sort_order, the tuples with sort_order=7 returned in ‘wrong’ order:
- the row with id=20 in front of the row with id=8
The tuples with sort_order=8 returned in ‘correct’ order:
- the row with id=9 in front of the row with id=21
After deleting the last 10 rows with id>21, we can see the situation reversed:
The tuples with sort_order=7 returned in ‘correct’ order:
- the row with id=8 in front of the row with id=20
The tuples with sort_order=8 returned in ‘wrong’ order:
- the row with id=21 in front of the row with id=9
After deleting 9 more rows with sort_order>9, now we can see all the 4 tuples returned in ‘correct’ order:
- the row with id=8 in front of the row with id=20
- the row with id=9 in front of the row with id=21
How did this happen?
Without diving into the code deeply, I will explain by highlighting the documentation for MySQL 5.7 ORDER BY optimization.
Citing the key sections below:
“MySQL has two filesort algorithms for sorting and retrieving results. The original method uses only the ORDER BY columns. The modified method uses not just the ORDER BY columns, but all the columns referenced by the query.”
“... the optimizer uses the modified algorithm only if the total size of the extra columns in the sort tuple does not exceed the value of the max_length_for_sort_data system variable.”
“The modified filesort algorithm incorporates an optimization to avoid reading the rows twice: It records the sort key value, but instead of the row ID, it records the columns referenced by the query” “...When the sort buffer becomes full, sort the tuples by sort key value in memory and write it to a temporary file.”
Here is the explanation
Let's take a look at the filesort_summary for each query that the optimizer follows:
When the query select all the columns, because the tuple in the example table is over the max_length_for_sort_data which is set to 1024, it selected the original filesort method and so the results were returned in ‘correct’ order based on rowId:
"filesort_summary": {
"rows": 31,
"examined_rows": 31,
"number_of_tmp_files": 0,
"sort_buffer_size": 6096,
"sort_mode": "sort_key, rowid"
}
When the query selected part of the columns, because the total size of the extra columns is lower than the max_length_for_sort_data (which is set to 1024 by default), MySQL used the modified file sort algorithm. Since it sorted the tuples by sort key value (the order by column sort_order), which has duplicated values, the algorithm may return the rows in random order. In some circumstances, MySQL may return the results in the ‘wrong’ order, while in others, MySQL may return the results in the ‘correct’ order.
"filesort_summary": {
"rows": 31,
"examined_rows": 31,
"number_of_tmp_files": 0,
"sort_buffer_size": 99822,
"sort_mode": "sort_key, additional_fields"
}
When our example query has a WHERE clause, so that there are only a few rows being sorted, in the case that most likely the results were returned in ‘correct’ order.
#
"filesort_summary": {
"rows": 4,
"examined_rows": 31,
"number_of_tmp_files": 0,
"sort_buffer_size": 99822,
"sort_mode": "sort_key, additional_fields"
}
After deleting most of the rows, there are only a few rows being sorted, in the case that most likely the results were returned in ‘correct’ order.
"filesort_summary": {
"rows": 12,
"examined_rows": 12,
"number_of_tmp_files": 0,
"sort_buffer_size": 99822,
"sort_mode": "sort_key, additional_fields"
}
It is true that since multiple rows have identical values in the ORDER BY column, the server is free to return those rows in any order. In other words, the sort order of those rows is non-deterministic due to the non-unique order by column.
Bearing this in mind, if you use LIMIT N, M following with ORDER BY non-unique column, you may get unexpected results based on different circumstances such as sort buffer related variables, the data size of each tuple, number of rows returned, data size returned etc. All of these factors might affect the choice of the file sort algorithm or even differences inside the algorithm itself. See this bug for more information: https://bugs.mysql.com/bug.php?id=72076
But why did MySQL 5.7 return the results in the order differently? Again, the relevant documentation hints at this:
“As of MySQL 5.7.3, the modified filesort algorithm includes an additional optimization designed to enable more tuples to fit into the sort buffer...”
And here is the optimizer filesort_summary on the query returning the results in the order differently:
# "filesort_summary": {
"rows": 31,
"examined_rows": 31,
"number_of_tmp_files": 0,
"sort_buffer_size": 100208,
"sort_mode": "sort_key, packed_additional_fields"
}
Conclusion
For MySQL, results may be returned in a non-deterministic order with 'ORDER BY' on a non-unique column. The only reliable way to get the expected order of your results is to explicitly define your query's ORDER BY clause to handle potential duplicate tuples. Then test it.
In the end, ordering by unique columns will always return results in deterministic order.
Pythian offers end-to-end MySQL Services from planning and Implementation to ongoing operational support. Contact us to learn more.
