| 2014-04-24 06:15:52 |
winchester |
bug |
|
|
added bug |
| 2014-04-24 06:19:01 |
winchester |
description |
server version is 5.6.15-56-log Percona Server (GPL), Release rel63.0, Revision 519
my table looks like:
CREATE TABLE `mentor_appren_rewards` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`rewardable_type` varchar(30) DEFAULT NULL,
`rewardable_id` int(11) DEFAULT NULL,
`apprentice_id` int(11) DEFAULT NULL,
`reward_content` varchar(30) DEFAULT NULL,
`reward_id` int(11) DEFAULT NULL,
`state` varchar(30) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_mentor_appren_rewards_on_rewardable_type_and_rewardable_id` (`rewardable_type`,`rewardable_id`),
KEY `index_mentor_appren_rewards_on_apprentice_id` (`apprentice_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6306 DEFAULT CHARSET=utf8
1. query without limit:
SELECT id FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content;
explain result:
id: 1
select_type: SIMPLE
table: mentor_appren_rewards
type: ref
possible_keys: index_mentor_appren_rewards_on_rewardable_type_and_rewardable_id
key: index_mentor_appren_rewards_on_rewardable_type_and_rewardable_id
key_len: 98
ref: const,const
rows: 18
Extra: Using index condition; Using where; Using filesort
query result:
+-----+
| id |
+-----+
| 96 |
| 85 |
| 98 |
| 89 |
| 102 |
| 117 |
| 104 |
| 91 |
| 107 |
| 81 |
| 94 |
| 83 |
| 109 |
| 111 |
| 87 |
| 113 |
| 100 |
| 115 |
+-----+
18 rows in set (0.00 sec)
2. now with limit(explain results are identicla):
SELECT id FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 0,8;
+-----+
| id |
+-----+
| 96 |
| 85 |
| 98 |
| 102 |
| 89 |
| 117 |
| 104 |
| 91 |
+-----+
8 rows in set (0.00 sec)
SELECT id FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 8,8;
+-----+
| id |
+-----+
| 94 |
| 107 |
| 81 |
| 83 |
| 109 |
| 111 |
| 87 |
| 100 |
+-----+
8 rows in set (0.00 sec)
SELECT id FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 16,8;
+-----+
| id |
+-----+
| 100 |
| 115 |
+-----+
2 rows in set (0.00 sec)
With limit, the order is slightly different with the first query(compare query 1 with `limit 0,8`). More importantly, see `limit 8,8` and `limit 16,8`, ID 100 appears in both result.
PS: `order by state, reward_content, id` works |
server version is 5.6.15-56-log Percona Server (GPL), Release rel63.0, Revision 519
my table looks like:
CREATE TABLE `mentor_appren_rewards` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`rewardable_type` varchar(30) DEFAULT NULL,
`rewardable_id` int(11) DEFAULT NULL,
`apprentice_id` int(11) DEFAULT NULL,
`reward_content` varchar(30) DEFAULT NULL,
`reward_id` int(11) DEFAULT NULL,
`state` varchar(30) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_mentor_appren_rewards_on_rewardable_type_and_rewardable_id` (`rewardable_type`,`rewardable_id`),
KEY `index_mentor_appren_rewards_on_apprentice_id` (`apprentice_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6306 DEFAULT CHARSET=utf8
1. query without limit:
SELECT id FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content;
explain result:
id: 1
select_type: SIMPLE
table: mentor_appren_rewards
type: ref
possible_keys: index_mentor_appren_rewards_on_rewardable_type_and_rewardable_id
key: index_mentor_appren_rewards_on_rewardable_type_and_rewardable_id
key_len: 98
ref: const,const
rows: 18
Extra: Using index condition; Using where; Using filesort
query result:
+-----+
| id |
+-----+
| 96 |
| 85 |
| 98 |
| 89 |
| 102 |
| 117 |
| 104 |
| 91 |
| 107 |
| 81 |
| 94 |
| 83 |
| 109 |
| 111 |
| 87 |
| 113 |
| 100 |
| 115 |
+-----+
18 rows in set (0.00 sec)
2. now with limit(explain results are identical):
SELECT id FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 0,8;
+-----+
| id |
+-----+
| 96 |
| 85 |
| 98 |
| 102 |
| 89 |
| 117 |
| 104 |
| 91 |
+-----+
8 rows in set (0.00 sec)
SELECT id FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 8,8;
+-----+
| id |
+-----+
| 94 |
| 107 |
| 81 |
| 83 |
| 109 |
| 111 |
| 87 |
| 100 |
+-----+
8 rows in set (0.00 sec)
SELECT id FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 16,8;
+-----+
| id |
+-----+
| 100 |
| 115 |
+-----+
2 rows in set (0.00 sec)
With limit, the order is slightly different with the first query(compare query 1 with `limit 0,8`). More importantly, see `limit 8,8` and `limit 16,8`, ID 100 appears in both result, which is a big trouble for things like pagination.
PS: `order by state, reward_content, id` works |
|
| 2014-04-24 06:19:42 |
winchester |
description |
server version is 5.6.15-56-log Percona Server (GPL), Release rel63.0, Revision 519
my table looks like:
CREATE TABLE `mentor_appren_rewards` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`rewardable_type` varchar(30) DEFAULT NULL,
`rewardable_id` int(11) DEFAULT NULL,
`apprentice_id` int(11) DEFAULT NULL,
`reward_content` varchar(30) DEFAULT NULL,
`reward_id` int(11) DEFAULT NULL,
`state` varchar(30) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_mentor_appren_rewards_on_rewardable_type_and_rewardable_id` (`rewardable_type`,`rewardable_id`),
KEY `index_mentor_appren_rewards_on_apprentice_id` (`apprentice_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6306 DEFAULT CHARSET=utf8
1. query without limit:
SELECT id FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content;
explain result:
id: 1
select_type: SIMPLE
table: mentor_appren_rewards
type: ref
possible_keys: index_mentor_appren_rewards_on_rewardable_type_and_rewardable_id
key: index_mentor_appren_rewards_on_rewardable_type_and_rewardable_id
key_len: 98
ref: const,const
rows: 18
Extra: Using index condition; Using where; Using filesort
query result:
+-----+
| id |
+-----+
| 96 |
| 85 |
| 98 |
| 89 |
| 102 |
| 117 |
| 104 |
| 91 |
| 107 |
| 81 |
| 94 |
| 83 |
| 109 |
| 111 |
| 87 |
| 113 |
| 100 |
| 115 |
+-----+
18 rows in set (0.00 sec)
2. now with limit(explain results are identical):
SELECT id FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 0,8;
+-----+
| id |
+-----+
| 96 |
| 85 |
| 98 |
| 102 |
| 89 |
| 117 |
| 104 |
| 91 |
+-----+
8 rows in set (0.00 sec)
SELECT id FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 8,8;
+-----+
| id |
+-----+
| 94 |
| 107 |
| 81 |
| 83 |
| 109 |
| 111 |
| 87 |
| 100 |
+-----+
8 rows in set (0.00 sec)
SELECT id FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 16,8;
+-----+
| id |
+-----+
| 100 |
| 115 |
+-----+
2 rows in set (0.00 sec)
With limit, the order is slightly different with the first query(compare query 1 with `limit 0,8`). More importantly, see `limit 8,8` and `limit 16,8`, ID 100 appears in both result, which is a big trouble for things like pagination.
PS: `order by state, reward_content, id` works |
server version is 5.6.15-56-log Percona Server (GPL), Release rel63.0, Revision 519
my table looks like:
CREATE TABLE `mentor_appren_rewards` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`rewardable_type` varchar(30) DEFAULT NULL,
`rewardable_id` int(11) DEFAULT NULL,
`apprentice_id` int(11) DEFAULT NULL,
`reward_content` varchar(30) DEFAULT NULL,
`reward_id` int(11) DEFAULT NULL,
`state` varchar(30) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_mentor_appren_rewards_on_rewardable_type_and_rewardable_id` (`rewardable_type`,`rewardable_id`),
KEY `index_mentor_appren_rewards_on_apprentice_id` (`apprentice_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6306 DEFAULT CHARSET=utf8
1. query without limit:
SELECT id FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content;
explain result:
id: 1
select_type: SIMPLE
table: mentor_appren_rewards
type: ref
possible_keys: index_mentor_appren_rewards_on_rewardable_type_and_rewardable_id
key: index_mentor_appren_rewards_on_rewardable_type_and_rewardable_id
key_len: 98
ref: const,const
rows: 18
Extra: Using index condition; Using where; Using filesort
query result:
+-----+
| id |
+-----+
| 96 |
| 85 |
| 98 |
| 89 |
| 102 |
| 117 |
| 104 |
| 91 |
| 107 |
| 81 |
| 94 |
| 83 |
| 109 |
| 111 |
| 87 |
| 113 |
| 100 |
| 115 |
+-----+
18 rows in set (0.00 sec)
2. now with limit(explain results are identical):
SELECT id FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 0,8;
+-----+
| id |
+-----+
| 96 |
| 85 |
| 98 |
| 102 |
| 89 |
| 117 |
| 104 |
| 91 |
+-----+
8 rows in set (0.00 sec)
SELECT id FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 8,8;
+-----+
| id |
+-----+
| 94 |
| 107 |
| 81 |
| 83 |
| 109 |
| 111 |
| 87 |
| 100 |
+-----+
8 rows in set (0.00 sec)
SELECT id FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 16,8;
+-----+
| id |
+-----+
| 100 |
| 115 |
+-----+
2 rows in set (0.00 sec)
With limit, the order is slightly different with the first query(compare query 1 with `limit 0,8`). More importantly, see the result of `limit 8,8` and `limit 16,8`, ID 100 appears in both results, which is a big trouble for things like pagination.
PS: `order by state, reward_content, id` works |
|
| 2014-04-24 06:38:20 |
winchester |
description |
server version is 5.6.15-56-log Percona Server (GPL), Release rel63.0, Revision 519
my table looks like:
CREATE TABLE `mentor_appren_rewards` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`rewardable_type` varchar(30) DEFAULT NULL,
`rewardable_id` int(11) DEFAULT NULL,
`apprentice_id` int(11) DEFAULT NULL,
`reward_content` varchar(30) DEFAULT NULL,
`reward_id` int(11) DEFAULT NULL,
`state` varchar(30) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_mentor_appren_rewards_on_rewardable_type_and_rewardable_id` (`rewardable_type`,`rewardable_id`),
KEY `index_mentor_appren_rewards_on_apprentice_id` (`apprentice_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6306 DEFAULT CHARSET=utf8
1. query without limit:
SELECT id FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content;
explain result:
id: 1
select_type: SIMPLE
table: mentor_appren_rewards
type: ref
possible_keys: index_mentor_appren_rewards_on_rewardable_type_and_rewardable_id
key: index_mentor_appren_rewards_on_rewardable_type_and_rewardable_id
key_len: 98
ref: const,const
rows: 18
Extra: Using index condition; Using where; Using filesort
query result:
+-----+
| id |
+-----+
| 96 |
| 85 |
| 98 |
| 89 |
| 102 |
| 117 |
| 104 |
| 91 |
| 107 |
| 81 |
| 94 |
| 83 |
| 109 |
| 111 |
| 87 |
| 113 |
| 100 |
| 115 |
+-----+
18 rows in set (0.00 sec)
2. now with limit(explain results are identical):
SELECT id FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 0,8;
+-----+
| id |
+-----+
| 96 |
| 85 |
| 98 |
| 102 |
| 89 |
| 117 |
| 104 |
| 91 |
+-----+
8 rows in set (0.00 sec)
SELECT id FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 8,8;
+-----+
| id |
+-----+
| 94 |
| 107 |
| 81 |
| 83 |
| 109 |
| 111 |
| 87 |
| 100 |
+-----+
8 rows in set (0.00 sec)
SELECT id FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 16,8;
+-----+
| id |
+-----+
| 100 |
| 115 |
+-----+
2 rows in set (0.00 sec)
With limit, the order is slightly different with the first query(compare query 1 with `limit 0,8`). More importantly, see the result of `limit 8,8` and `limit 16,8`, ID 100 appears in both results, which is a big trouble for things like pagination.
PS: `order by state, reward_content, id` works |
server version is 5.6.15-56-log Percona Server (GPL), Release rel63.0, Revision 519
my table looks like:
CREATE TABLE `mentor_appren_rewards` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`rewardable_type` varchar(30) DEFAULT NULL,
`rewardable_id` int(11) DEFAULT NULL,
`apprentice_id` int(11) DEFAULT NULL,
`reward_content` varchar(30) DEFAULT NULL,
`reward_id` int(11) DEFAULT NULL,
`state` varchar(30) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_mentor_appren_rewards_on_rewardable_type_and_rewardable_id` (`rewardable_type`,`rewardable_id`),
KEY `index_mentor_appren_rewards_on_apprentice_id` (`apprentice_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6306 DEFAULT CHARSET=utf8
1. query without limit:
SELECT id, state, reward_content FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content;
explain result:
id: 1
select_type: SIMPLE
table: mentor_appren_rewards
type: ref
possible_keys: index_mentor_appren_rewards_on_rewardable_type_and_rewardable_id
key: index_mentor_appren_rewards_on_rewardable_type_and_rewardable_id
key_len: 98
ref: const,const
rows: 18
Extra: Using index condition; Using where; Using filesort
query result:
+-----+----------+----------------+
| id | state | reward_content |
+-----+----------+----------------+
| 96 | pending | 1_level_08 |
| 85 | pending | 1_level_10 |
| 98 | pending | 1_level_10 |
| 89 | pending | 2_elo_0800 |
| 102 | pending | 2_elo_0800 |
| 117 | pending | 2_elo_1000 |
| 104 | pending | 2_elo_1000 |
| 91 | pending | 2_elo_1000 |
| 107 | rewarded | 1_level_05 |
| 81 | rewarded | 1_level_05 |
| 94 | rewarded | 1_level_05 |
| 83 | rewarded | 1_level_08 |
| 109 | rewarded | 1_level_08 |
| 111 | rewarded | 1_level_10 |
| 87 | rewarded | 2_elo_0500 |
| 113 | rewarded | 2_elo_0500 |
| 100 | rewarded | 2_elo_0500 |
| 115 | rewarded | 2_elo_0800 |
+-----+----------+----------------+
18 rows in set (0.00 sec)
2. now with limit(explain results are identical):
SELECT id, state, reward_content FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 0,8;
+-----+---------+----------------+
| id | state | reward_content |
+-----+---------+----------------+
| 96 | pending | 1_level_08 |
| 85 | pending | 1_level_10 |
| 98 | pending | 1_level_10 |
| 102 | pending | 2_elo_0800 |
| 89 | pending | 2_elo_0800 |
| 117 | pending | 2_elo_1000 |
| 104 | pending | 2_elo_1000 |
| 91 | pending | 2_elo_1000 |
+-----+---------+----------------+
8 rows in set (0.00 sec)
SELECT id, state, reward_content FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 8,8;
+-----+----------+----------------+
| id | state | reward_content |
+-----+----------+----------------+
| 94 | rewarded | 1_level_05 |
| 107 | rewarded | 1_level_05 |
| 81 | rewarded | 1_level_05 |
| 83 | rewarded | 1_level_08 |
| 109 | rewarded | 1_level_08 |
| 111 | rewarded | 1_level_10 |
| 87 | rewarded | 2_elo_0500 |
| 100 | rewarded | 2_elo_0500 |
+-----+----------+----------------+
8 rows in set (0.00 sec)
SELECT id, state, reward_content FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 16,8;
+-----+----------+----------------+
| id | state | reward_content |
+-----+----------+----------------+
| 100 | rewarded | 2_elo_0500 |
| 115 | rewarded | 2_elo_0800 |
+-----+----------+----------------+
2 rows in set (0.00 sec)
Isn't with MySQL, `limit 16, 8` means fetching the first 24 rows, then dumping the first 16 rows? If so, why should those rows with same `state` and `reward` are ordered seemingly randomly with different `limit`? What's more worse is that in this case, ID 100 appears in both `limit 8,8` and `limit 16, 8`, while ID 113 disappears. This is really bad for things like pagination.
PS: In this case, I can simply use `order by state, reward_content, id` |
|
| 2014-04-24 06:40:34 |
winchester |
description |
server version is 5.6.15-56-log Percona Server (GPL), Release rel63.0, Revision 519
my table looks like:
CREATE TABLE `mentor_appren_rewards` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`rewardable_type` varchar(30) DEFAULT NULL,
`rewardable_id` int(11) DEFAULT NULL,
`apprentice_id` int(11) DEFAULT NULL,
`reward_content` varchar(30) DEFAULT NULL,
`reward_id` int(11) DEFAULT NULL,
`state` varchar(30) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_mentor_appren_rewards_on_rewardable_type_and_rewardable_id` (`rewardable_type`,`rewardable_id`),
KEY `index_mentor_appren_rewards_on_apprentice_id` (`apprentice_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6306 DEFAULT CHARSET=utf8
1. query without limit:
SELECT id, state, reward_content FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content;
explain result:
id: 1
select_type: SIMPLE
table: mentor_appren_rewards
type: ref
possible_keys: index_mentor_appren_rewards_on_rewardable_type_and_rewardable_id
key: index_mentor_appren_rewards_on_rewardable_type_and_rewardable_id
key_len: 98
ref: const,const
rows: 18
Extra: Using index condition; Using where; Using filesort
query result:
+-----+----------+----------------+
| id | state | reward_content |
+-----+----------+----------------+
| 96 | pending | 1_level_08 |
| 85 | pending | 1_level_10 |
| 98 | pending | 1_level_10 |
| 89 | pending | 2_elo_0800 |
| 102 | pending | 2_elo_0800 |
| 117 | pending | 2_elo_1000 |
| 104 | pending | 2_elo_1000 |
| 91 | pending | 2_elo_1000 |
| 107 | rewarded | 1_level_05 |
| 81 | rewarded | 1_level_05 |
| 94 | rewarded | 1_level_05 |
| 83 | rewarded | 1_level_08 |
| 109 | rewarded | 1_level_08 |
| 111 | rewarded | 1_level_10 |
| 87 | rewarded | 2_elo_0500 |
| 113 | rewarded | 2_elo_0500 |
| 100 | rewarded | 2_elo_0500 |
| 115 | rewarded | 2_elo_0800 |
+-----+----------+----------------+
18 rows in set (0.00 sec)
2. now with limit(explain results are identical):
SELECT id, state, reward_content FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 0,8;
+-----+---------+----------------+
| id | state | reward_content |
+-----+---------+----------------+
| 96 | pending | 1_level_08 |
| 85 | pending | 1_level_10 |
| 98 | pending | 1_level_10 |
| 102 | pending | 2_elo_0800 |
| 89 | pending | 2_elo_0800 |
| 117 | pending | 2_elo_1000 |
| 104 | pending | 2_elo_1000 |
| 91 | pending | 2_elo_1000 |
+-----+---------+----------------+
8 rows in set (0.00 sec)
SELECT id, state, reward_content FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 8,8;
+-----+----------+----------------+
| id | state | reward_content |
+-----+----------+----------------+
| 94 | rewarded | 1_level_05 |
| 107 | rewarded | 1_level_05 |
| 81 | rewarded | 1_level_05 |
| 83 | rewarded | 1_level_08 |
| 109 | rewarded | 1_level_08 |
| 111 | rewarded | 1_level_10 |
| 87 | rewarded | 2_elo_0500 |
| 100 | rewarded | 2_elo_0500 |
+-----+----------+----------------+
8 rows in set (0.00 sec)
SELECT id, state, reward_content FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 16,8;
+-----+----------+----------------+
| id | state | reward_content |
+-----+----------+----------------+
| 100 | rewarded | 2_elo_0500 |
| 115 | rewarded | 2_elo_0800 |
+-----+----------+----------------+
2 rows in set (0.00 sec)
Isn't with MySQL, `limit 16, 8` means fetching the first 24 rows, then dumping the first 16 rows? If so, why should those rows with same `state` and `reward` are ordered seemingly randomly with different `limit`? What's more worse is that in this case, ID 100 appears in both `limit 8,8` and `limit 16, 8`, while ID 113 disappears. This is really bad for things like pagination.
PS: In this case, I can simply use `order by state, reward_content, id` |
server version is 5.6.15-56-log Percona Server (GPL), Release rel63.0, Revision 519
my table looks like:
CREATE TABLE `mentor_appren_rewards` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`rewardable_type` varchar(30) DEFAULT NULL,
`rewardable_id` int(11) DEFAULT NULL,
`apprentice_id` int(11) DEFAULT NULL,
`reward_content` varchar(30) DEFAULT NULL,
`reward_id` int(11) DEFAULT NULL,
`state` varchar(30) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_mentor_appren_rewards_on_rewardable_type_and_rewardable_id` (`rewardable_type`,`rewardable_id`),
KEY `index_mentor_appren_rewards_on_apprentice_id` (`apprentice_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6306 DEFAULT CHARSET=utf8
1. query without limit:
SELECT id, state, reward_content FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content;
explain result:
id: 1
select_type: SIMPLE
table: mentor_appren_rewards
type: ref
possible_keys: index_mentor_appren_rewards_on_rewardable_type_and_rewardable_id
key: index_mentor_appren_rewards_on_rewardable_type_and_rewardable_id
key_len: 98
ref: const,const
rows: 18
Extra: Using index condition; Using where; Using filesort
query result:
+-----+----------+----------------+
| id | state | reward_content |
+-----+----------+----------------+
| 96 | pending | 1_level_08 |
| 85 | pending | 1_level_10 |
| 98 | pending | 1_level_10 |
| 89 | pending | 2_elo_0800 |
| 102 | pending | 2_elo_0800 |
| 117 | pending | 2_elo_1000 |
| 104 | pending | 2_elo_1000 |
| 91 | pending | 2_elo_1000 |
| 107 | rewarded | 1_level_05 |
| 81 | rewarded | 1_level_05 |
| 94 | rewarded | 1_level_05 |
| 83 | rewarded | 1_level_08 |
| 109 | rewarded | 1_level_08 |
| 111 | rewarded | 1_level_10 |
| 87 | rewarded | 2_elo_0500 |
| 113 | rewarded | 2_elo_0500 |
| 100 | rewarded | 2_elo_0500 |
| 115 | rewarded | 2_elo_0800 |
+-----+----------+----------------+
18 rows in set (0.00 sec)
2. now with limit(explain results are identical):
SELECT id, state, reward_content FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 0,8;
+-----+---------+----------------+
| id | state | reward_content |
+-----+---------+----------------+
| 96 | pending | 1_level_08 |
| 85 | pending | 1_level_10 |
| 98 | pending | 1_level_10 |
| 102 | pending | 2_elo_0800 |
| 89 | pending | 2_elo_0800 |
| 117 | pending | 2_elo_1000 |
| 104 | pending | 2_elo_1000 |
| 91 | pending | 2_elo_1000 |
+-----+---------+----------------+
8 rows in set (0.00 sec)
SELECT id, state, reward_content FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 8,8;
+-----+----------+----------------+
| id | state | reward_content |
+-----+----------+----------------+
| 94 | rewarded | 1_level_05 |
| 107 | rewarded | 1_level_05 |
| 81 | rewarded | 1_level_05 |
| 83 | rewarded | 1_level_08 |
| 109 | rewarded | 1_level_08 |
| 111 | rewarded | 1_level_10 |
| 87 | rewarded | 2_elo_0500 |
| 100 | rewarded | 2_elo_0500 |
+-----+----------+----------------+
8 rows in set (0.00 sec)
SELECT id, state, reward_content FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 16,8;
+-----+----------+----------------+
| id | state | reward_content |
+-----+----------+----------------+
| 100 | rewarded | 2_elo_0500 |
| 115 | rewarded | 2_elo_0800 |
+-----+----------+----------------+
2 rows in set (0.00 sec)
Isn't with MySQL, `limit 16, 8` means fetching the first 24 rows, then dumping the first 16 rows? If so, why should those rows with same `state` and `reward` are ordered seemingly randomly with different `limit`? What's more worse is that in this case, ID 100 appears in both `limit 8,8` and `limit 16, 8`, while ID 113 disappears. This is really bad for things like pagination.
PS: In this case, I can simply use `order by state, reward_content, id` to ensure consistent order |
|
| 2014-04-24 06:43:10 |
winchester |
description |
server version is 5.6.15-56-log Percona Server (GPL), Release rel63.0, Revision 519
my table looks like:
CREATE TABLE `mentor_appren_rewards` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`rewardable_type` varchar(30) DEFAULT NULL,
`rewardable_id` int(11) DEFAULT NULL,
`apprentice_id` int(11) DEFAULT NULL,
`reward_content` varchar(30) DEFAULT NULL,
`reward_id` int(11) DEFAULT NULL,
`state` varchar(30) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_mentor_appren_rewards_on_rewardable_type_and_rewardable_id` (`rewardable_type`,`rewardable_id`),
KEY `index_mentor_appren_rewards_on_apprentice_id` (`apprentice_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6306 DEFAULT CHARSET=utf8
1. query without limit:
SELECT id, state, reward_content FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content;
explain result:
id: 1
select_type: SIMPLE
table: mentor_appren_rewards
type: ref
possible_keys: index_mentor_appren_rewards_on_rewardable_type_and_rewardable_id
key: index_mentor_appren_rewards_on_rewardable_type_and_rewardable_id
key_len: 98
ref: const,const
rows: 18
Extra: Using index condition; Using where; Using filesort
query result:
+-----+----------+----------------+
| id | state | reward_content |
+-----+----------+----------------+
| 96 | pending | 1_level_08 |
| 85 | pending | 1_level_10 |
| 98 | pending | 1_level_10 |
| 89 | pending | 2_elo_0800 |
| 102 | pending | 2_elo_0800 |
| 117 | pending | 2_elo_1000 |
| 104 | pending | 2_elo_1000 |
| 91 | pending | 2_elo_1000 |
| 107 | rewarded | 1_level_05 |
| 81 | rewarded | 1_level_05 |
| 94 | rewarded | 1_level_05 |
| 83 | rewarded | 1_level_08 |
| 109 | rewarded | 1_level_08 |
| 111 | rewarded | 1_level_10 |
| 87 | rewarded | 2_elo_0500 |
| 113 | rewarded | 2_elo_0500 |
| 100 | rewarded | 2_elo_0500 |
| 115 | rewarded | 2_elo_0800 |
+-----+----------+----------------+
18 rows in set (0.00 sec)
2. now with limit(explain results are identical):
SELECT id, state, reward_content FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 0,8;
+-----+---------+----------------+
| id | state | reward_content |
+-----+---------+----------------+
| 96 | pending | 1_level_08 |
| 85 | pending | 1_level_10 |
| 98 | pending | 1_level_10 |
| 102 | pending | 2_elo_0800 |
| 89 | pending | 2_elo_0800 |
| 117 | pending | 2_elo_1000 |
| 104 | pending | 2_elo_1000 |
| 91 | pending | 2_elo_1000 |
+-----+---------+----------------+
8 rows in set (0.00 sec)
SELECT id, state, reward_content FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 8,8;
+-----+----------+----------------+
| id | state | reward_content |
+-----+----------+----------------+
| 94 | rewarded | 1_level_05 |
| 107 | rewarded | 1_level_05 |
| 81 | rewarded | 1_level_05 |
| 83 | rewarded | 1_level_08 |
| 109 | rewarded | 1_level_08 |
| 111 | rewarded | 1_level_10 |
| 87 | rewarded | 2_elo_0500 |
| 100 | rewarded | 2_elo_0500 |
+-----+----------+----------------+
8 rows in set (0.00 sec)
SELECT id, state, reward_content FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 16,8;
+-----+----------+----------------+
| id | state | reward_content |
+-----+----------+----------------+
| 100 | rewarded | 2_elo_0500 |
| 115 | rewarded | 2_elo_0800 |
+-----+----------+----------------+
2 rows in set (0.00 sec)
Isn't with MySQL, `limit 16, 8` means fetching the first 24 rows, then dumping the first 16 rows? If so, why should those rows with same `state` and `reward` are ordered seemingly randomly with different `limit`? What's more worse is that in this case, ID 100 appears in both `limit 8,8` and `limit 16, 8`, while ID 113 disappears. This is really bad for things like pagination.
PS: In this case, I can simply use `order by state, reward_content, id` to ensure consistent order |
server version is 5.6.15-56-log Percona Server (GPL), Release rel63.0, Revision 519
my table looks like:
CREATE TABLE `mentor_appren_rewards` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`rewardable_type` varchar(30) DEFAULT NULL,
`rewardable_id` int(11) DEFAULT NULL,
`apprentice_id` int(11) DEFAULT NULL,
`reward_content` varchar(30) DEFAULT NULL,
`reward_id` int(11) DEFAULT NULL,
`state` varchar(30) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_mentor_appren_rewards_on_rewardable_type_and_rewardable_id` (`rewardable_type`,`rewardable_id`),
KEY `index_mentor_appren_rewards_on_apprentice_id` (`apprentice_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6306 DEFAULT CHARSET=utf8
1. query without limit:
SELECT id, state, reward_content FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content;
explain result:
id: 1
select_type: SIMPLE
table: mentor_appren_rewards
type: ref
possible_keys: index_mentor_appren_rewards_on_rewardable_type_and_rewardable_id
key: index_mentor_appren_rewards_on_rewardable_type_and_rewardable_id
key_len: 98
ref: const,const
rows: 18
Extra: Using index condition; Using where; Using filesort
query result:
+-----+----------+----------------+
| id | state | reward_content |
+-----+----------+----------------+
| 96 | pending | 1_level_08 |
| 85 | pending | 1_level_10 |
| 98 | pending | 1_level_10 |
| 89 | pending | 2_elo_0800 |
| 102 | pending | 2_elo_0800 |
| 117 | pending | 2_elo_1000 |
| 104 | pending | 2_elo_1000 |
| 91 | pending | 2_elo_1000 |
| 107 | rewarded | 1_level_05 |
| 81 | rewarded | 1_level_05 |
| 94 | rewarded | 1_level_05 |
| 83 | rewarded | 1_level_08 |
| 109 | rewarded | 1_level_08 |
| 111 | rewarded | 1_level_10 |
| 87 | rewarded | 2_elo_0500 |
| 113 | rewarded | 2_elo_0500 |
| 100 | rewarded | 2_elo_0500 |
| 115 | rewarded | 2_elo_0800 |
+-----+----------+----------------+
18 rows in set (0.00 sec)
2. now with limit(explain results are identical):
SELECT id, state, reward_content FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 0,8;
+-----+---------+----------------+
| id | state | reward_content |
+-----+---------+----------------+
| 96 | pending | 1_level_08 |
| 85 | pending | 1_level_10 |
| 98 | pending | 1_level_10 |
| 102 | pending | 2_elo_0800 |
| 89 | pending | 2_elo_0800 |
| 117 | pending | 2_elo_1000 |
| 104 | pending | 2_elo_1000 |
| 91 | pending | 2_elo_1000 |
+-----+---------+----------------+
8 rows in set (0.00 sec)
SELECT id, state, reward_content FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 8,8;
+-----+----------+----------------+
| id | state | reward_content |
+-----+----------+----------------+
| 94 | rewarded | 1_level_05 |
| 107 | rewarded | 1_level_05 |
| 81 | rewarded | 1_level_05 |
| 83 | rewarded | 1_level_08 |
| 109 | rewarded | 1_level_08 |
| 111 | rewarded | 1_level_10 |
| 87 | rewarded | 2_elo_0500 |
| 100 | rewarded | 2_elo_0500 |
+-----+----------+----------------+
8 rows in set (0.00 sec)
SELECT id, state, reward_content FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 16,8;
+-----+----------+----------------+
| id | state | reward_content |
+-----+----------+----------------+
| 100 | rewarded | 2_elo_0500 |
| 115 | rewarded | 2_elo_0800 |
+-----+----------+----------------+
2 rows in set (0.00 sec)
Isn't with MySQL, `limit 16, 8` means fetching the first 24 rows, then dumping the first 16 rows? If so, why should those rows with same `state` and `reward` are ordered seemingly randomly with different `limit`? What's more worse is that in this case, ID 100 appears in both `limit 8,8` and `limit 16, 8`, while ID 113 disappears. This is really bad for application level things like pagination.
PS: In this case, I can simply use `order by state, reward_content, id` to ensure consistent order |
|
| 2014-06-06 12:26:26 |
Valerii Kravchuk |
nominated for series |
|
percona-server/5.6 |
|
| 2014-06-06 12:26:26 |
Valerii Kravchuk |
bug task added |
|
percona-server/5.6 |
|
| 2014-06-06 12:26:26 |
Valerii Kravchuk |
nominated for series |
|
percona-server/5.1 |
|
| 2014-06-06 12:26:26 |
Valerii Kravchuk |
bug task added |
|
percona-server/5.1 |
|
| 2014-06-06 12:26:26 |
Valerii Kravchuk |
nominated for series |
|
percona-server/5.5 |
|
| 2014-06-06 12:26:26 |
Valerii Kravchuk |
bug task added |
|
percona-server/5.5 |
|
| 2014-06-09 08:56:46 |
Nilnandan Joshi |
percona-server/5.6: status |
New |
Incomplete |
|
| 2014-06-09 08:56:49 |
Nilnandan Joshi |
percona-server/5.6: status |
Incomplete |
Confirmed |
|
| 2014-06-09 08:57:38 |
Nilnandan Joshi |
percona-server/5.1: status |
New |
Invalid |
|
| 2014-06-09 09:00:57 |
Nilnandan Joshi |
percona-server/5.5: status |
New |
Invalid |
|
| 2014-06-09 09:18:00 |
Nilnandan Joshi |
bug watch added |
|
http://bugs.mysql.com/bug.php?id=72917 |
|
| 2014-06-09 09:18:00 |
Nilnandan Joshi |
bug task added |
|
mysql-server |
|
| 2014-06-09 10:41:21 |
Laurynas Biveinis |
percona-server/5.6: importance |
Undecided |
High |
|
| 2014-06-09 10:41:23 |
Laurynas Biveinis |
percona-server/5.6: status |
Confirmed |
Triaged |
|
| 2014-06-09 10:41:29 |
Laurynas Biveinis |
tags |
|
upstream |
|
| 2014-07-31 18:33:31 |
Laurynas Biveinis |
percona-server/5.6: status |
Triaged |
Incomplete |
|
| 2016-03-24 16:06:14 |
Laurynas Biveinis |
nominated for series |
|
percona-server/5.7 |
|
| 2016-03-24 16:06:14 |
Laurynas Biveinis |
bug task added |
|
percona-server/5.7 |
|
