SELECT LIMIT 1 query returns unexpected results when the queried table field has an index-CodePudding

I have a table with an id field and some other fields. The id field has an index by default and this index seems to cause problems when i try to return the id value of the first row using the following query with a limit 1 clause:

SELECT cropvarietyname.id FROM `seedcalendar-test`.cropvarietyname LIMIT 1;

The first row has 1 as the id but instead of returning the value 1 this query returns the value 73. The row with id 73 is definitely not the first row! When i use EXPLAIN to analyze this query i get the following result:

id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'cropvarietyname', NULL, 'index', NULL, 'IDX_crop', '8', NULL, '433', '100.00', 'Using index'

For some reason the primary key index of id is used when running this query and it causes the id of row 73 to be returned instead of 1. When i replace the field id with a field that doesn't have an index (lastModified in the example below) then the field value of the first row is returned. So the following does work:

SELECT cropvarietyname.lastModified FROM `seedcalendar-test`.cropvarietyname LIMIT 1;

When i look at the extra field of the explain query result for this select query i can see that in this case no index is used. The following also works:

SELECT cropvarietyname.lastModified, cropvarietyname.id FROM `seedcalendar-test`.cropvarietyname LIMIT 1;

Here one queried field has an index (id) and one doesn't (lastModified). When i run it the id and lastModified values of the first row are correctly returned. But as soon as the LIMIT query contains nothing but indexed fields MySQL starts returning unexpected results because it starts using the index for some reason.

My question is: Why does this happen? There is no WHERE clause in the query so there should be no reason for MySQL to use one or more field indexes when executing this query.

This problem can be resolved by adding ORDER BY cropvarietyname.id ASC right before the LIMIT 1 clause. But i don't understand why i need to do this in the first place. Why doesn't MySQL return the value(s) of the first row when all the queried fields happen to be indexed and why does it suddenly start working correctly when you introduce atleast one unindexed field to the SELECT list?

Below is the code to recreate the table i've used in my examples. Although i must add that the same strange behavior can be seen when running a LIMIT query with an indexed field on any table.

CREATE TABLE `cropvarietyname` (
  `id` bigint(20) NOT NULL,
  `dateCreated` datetime DEFAULT NULL,
  `lastModified` datetime DEFAULT NULL,
  `lastAssessedOn` datetime DEFAULT NULL,
  `deleted` bit(1) NOT NULL,
  `submissionStatus` int(11) NOT NULL,
  `translatedName` varchar(255) DEFAULT NULL,
  `scientificName` varchar(255) DEFAULT NULL,
  `assessor_id` bigint(20) DEFAULT NULL,
  `language_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `FK_CROP` bigint(20) NOT NULL,
  `parent_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UC_parent_id_language_id_translatedName` (`parent_id`,`translatedName`,`language_id`),
  KEY `IDX_scientific_name` (`scientificName`),
  KEY `IDX_crop` (`FK_CROP`),
  KEY `IDX_name_crop` (`translatedName`,`FK_CROP`),
  KEY `IDX_cropvariety` (`parent_id`),
  KEY `IDX_cropvariety_language` (`language_id`,`parent_id`),
  KEY `IDX_submissionStatus_language` (`language_id`,`submissionStatus`),
  KEY `IDX_name_scientific_name` (`translatedName`,`scientificName`),
  KEY `IDX_crop_language` (`FK_CROP`,`language_id`),
  KEY `IDX_translator` (`user_id`),
  KEY `IDX_name_language` (`translatedName`,`language_id`),
  KEY `FKs7a71sapplqu96ntraxjen1f0` (`assessor_id`),
  CONSTRAINT `FK5veqd7kl96d3dj14uqs5w3atd` FOREIGN KEY (`parent_id`) REFERENCES `cropvariety` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK8hlx1suats5c60n5mcs2meue6` FOREIGN KEY (`user_id`) REFERENCES `members` (`id`),
  CONSTRAINT `FK99c5ih7r49epis56k3buksvv1` FOREIGN KEY (`FK_CROP`) REFERENCES `crop` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FKb536j3ril2noa5lepafnv7vc5` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`),
  CONSTRAINT `FKs7a71sapplqu96ntraxjen1f0` FOREIGN KEY (`assessor_id`) REFERENCES `members` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

After you've created the table you can use the following test data to populate it with. These insert statements only give you the first 17 rows of the entire test dataset of 433 rows but it should be enough to reproduce the problem:

INSERT INTO `` (`id`,`dateCreated`,`lastModified`,`lastAssessedOn`,`deleted`,`submissionStatus`,`translatedName`,`scientificName`,`assessor_id`,`language_id`,`user_id`,`FK_CROP`,`parent_id`) VALUES (1,'2022-08-16 23:05:29','2022-08-16 23:05:29',NULL,'0',4,'green cactus 5 30other2','31-1100583337',NULL,1,4,7,1);
INSERT INTO `` (`id`,`dateCreated`,`lastModified`,`lastAssessedOn`,`deleted`,`submissionStatus`,`translatedName`,`scientificName`,`assessor_id`,`language_id`,`user_id`,`FK_CROP`,`parent_id`) VALUES (2,'2022-08-16 23:05:29','2022-08-16 23:05:29',NULL,'0',4,'cactus vert 5 30','31927763221',NULL,21,4,7,1);
INSERT INTO `` (`id`,`dateCreated`,`lastModified`,`lastAssessedOn`,`deleted`,`submissionStatus`,`translatedName`,`scientificName`,`assessor_id`,`language_id`,`user_id`,`FK_CROP`,`parent_id`) VALUES (3,'2022-08-16 23:05:29','2022-08-16 23:05:29',NULL,'0',4,'green cactus 5 30','31826800418',NULL,1,4,7,1);
INSERT INTO `` (`id`,`dateCreated`,`lastModified`,`lastAssessedOn`,`deleted`,`submissionStatus`,`translatedName`,`scientificName`,`assessor_id`,`language_id`,`user_id`,`FK_CROP`,`parent_id`) VALUES (4,'2022-08-16 23:05:29','2022-08-16 23:05:29',NULL,'0',4,'green cactus 5 30other1','31-2130733867',NULL,1,4,7,1);
INSERT INTO `` (`id`,`dateCreated`,`lastModified`,`lastAssessedOn`,`deleted`,`submissionStatus`,`translatedName`,`scientificName`,`assessor_id`,`language_id`,`user_id`,`FK_CROP`,`parent_id`) VALUES (5,'2022-08-16 23:05:29','2022-08-16 23:05:29',NULL,'0',4,'grunne kaktus 5 30other1','31124416021',NULL,25,4,7,1);
INSERT INTO `` (`id`,`dateCreated`,`lastModified`,`lastAssessedOn`,`deleted`,`submissionStatus`,`translatedName`,`scientificName`,`assessor_id`,`language_id`,`user_id`,`FK_CROP`,`parent_id`) VALUES (6,'2022-08-16 23:05:29','2022-08-16 23:05:29',NULL,'0',4,'grunne kaktus 5 30','311170713725',NULL,25,4,7,1);
INSERT INTO `` (`id`,`dateCreated`,`lastModified`,`lastAssessedOn`,`deleted`,`submissionStatus`,`translatedName`,`scientificName`,`assessor_id`,`language_id`,`user_id`,`FK_CROP`,`parent_id`) VALUES (7,'2022-08-16 23:05:29','2022-08-16 23:05:29',NULL,'0',4,'groene cactus 5 30other1','314209744',NULL,17,4,7,1);
INSERT INTO `` (`id`,`dateCreated`,`lastModified`,`lastAssessedOn`,`deleted`,`submissionStatus`,`translatedName`,`scientificName`,`assessor_id`,`language_id`,`user_id`,`FK_CROP`,`parent_id`) VALUES (8,'2022-08-16 23:05:29','2022-08-16 23:05:29',NULL,'0',4,'groene cactus 5 30','31-1824444332',NULL,17,4,7,1);
INSERT INTO `` (`id`,`dateCreated`,`lastModified`,`lastAssessedOn`,`deleted`,`submissionStatus`,`translatedName`,`scientificName`,`assessor_id`,`language_id`,`user_id`,`FK_CROP`,`parent_id`) VALUES (9,'2022-08-16 23:05:29','2022-08-16 23:05:29',NULL,'0',4,'cactus vert 5 30other2','31555292418',NULL,21,4,7,1);
INSERT INTO `` (`id`,`dateCreated`,`lastModified`,`lastAssessedOn`,`deleted`,`submissionStatus`,`translatedName`,`scientificName`,`assessor_id`,`language_id`,`user_id`,`FK_CROP`,`parent_id`) VALUES (10,'2022-08-16 23:05:29','2022-08-16 23:05:29',NULL,'0',4,'grunne kaktus 5 30other2','31-736042183',NULL,25,4,7,1);
INSERT INTO `` (`id`,`dateCreated`,`lastModified`,`lastAssessedOn`,`deleted`,`submissionStatus`,`translatedName`,`scientificName`,`assessor_id`,`language_id`,`user_id`,`FK_CROP`,`parent_id`) VALUES (11,'2022-08-16 23:05:29','2022-08-16 23:05:29',NULL,'0',4,'cactus vert 5 30other1','31459711790',NULL,21,4,7,1);
INSERT INTO `` (`id`,`dateCreated`,`lastModified`,`lastAssessedOn`,`deleted`,`submissionStatus`,`translatedName`,`scientificName`,`assessor_id`,`language_id`,`user_id`,`FK_CROP`,`parent_id`) VALUES (12,'2022-08-16 23:05:29','2022-08-16 23:05:29',NULL,'0',4,'groene cactus 5 30other2','31458401709',NULL,17,4,7,1);
INSERT INTO `` (`id`,`dateCreated`,`lastModified`,`lastAssessedOn`,`deleted`,`submissionStatus`,`translatedName`,`scientificName`,`assessor_id`,`language_id`,`user_id`,`FK_CROP`,`parent_id`) VALUES (13,'2022-08-16 23:05:29','2022-08-16 23:05:29',NULL,'0',4,'basilic 7 30other1','311174402979',NULL,21,4,6,2);
INSERT INTO `` (`id`,`dateCreated`,`lastModified`,`lastAssessedOn`,`deleted`,`submissionStatus`,`translatedName`,`scientificName`,`assessor_id`,`language_id`,`user_id`,`FK_CROP`,`parent_id`) VALUES (14,'2022-08-16 23:05:29','2022-08-16 23:05:29',NULL,'0',4,'basilikum 7 30','311067296650',NULL,25,4,6,2);
INSERT INTO `` (`id`,`dateCreated`,`lastModified`,`lastAssessedOn`,`deleted`,`submissionStatus`,`translatedName`,`scientificName`,`assessor_id`,`language_id`,`user_id`,`FK_CROP`,`parent_id`) VALUES (15,'2022-08-16 23:05:29','2022-08-16 23:05:29',NULL,'0',4,'basel 7 30','311648762313',NULL,1,4,6,2);
INSERT INTO `` (`id`,`dateCreated`,`lastModified`,`lastAssessedOn`,`deleted`,`submissionStatus`,`translatedName`,`scientificName`,`assessor_id`,`language_id`,`user_id`,`FK_CROP`,`parent_id`) VALUES (16,'2022-08-16 23:05:29','2022-08-16 23:05:29',NULL,'0',4,'bascilicum 7 30','31-1839617752',NULL,17,4,6,2);
INSERT INTO `` (`id`,`dateCreated`,`lastModified`,`lastAssessedOn`,`deleted`,`submissionStatus`,`translatedName`,`scientificName`,`assessor_id`,`language_id`,`user_id`,`FK_CROP`,`parent_id`) VALUES (17,'2022-08-16 23:05:29','2022-08-16 23:05:29',NULL,'0',4,'basel 7 30other2','31-2027625143',NULL,1,4,6,2);

EDIT: I am using MySQL 8.

CodePudding user response：

It is a bug in your code to do selects without specifying an ORDER BY and expect some particular order. Conceptually, database tables are unordered data; there is no "first row". The database is free to return rows in any order it wants, and seemingly unrelated changes to your query may change that order, as can changing database versions.

CodePudding user response：

In practice, current versions of InnoDB reads rows in order by the index it uses to access the table. If no index is used, it reads the rows in order by the clustered index (that is, the primary key order).

Demo: I create a table that has a clustered index (id) and another indexed column (x), and another non-indexed column (y).

mysql> create table test (id serial primary key, x int, y int, key(x));

I fill 3 rows, such that the order of the primary key is opposite the order of the other rows:

mysql> insert into test values (1, 6, 13), (2, 5, 12), (3, 4, 11);

If I query all the columns without specifying an order, they are returned in primary key order:

mysql> select * from test;
 ---- ------ ------ 
| id | x    | y    |
 ---- ------ ------ 
|  1 |    6 |   13 |
|  2 |    5 |   12 |
|  3 |    4 |   11 |
 ---- ------ ------

If I query only the unindexed column y, they are also returned in primary key order:

mysql> select y from test;
 ------ 
| y    |
 ------ 
|   13 |
|   12 |
|   11 |
 ------

If I query only the indexed column x, the rows are read from the index on x, and are returned in that order:

mysql> select x from test;
 ------ 
| x    |
 ------ 
|    4 |
|    5 |
|    6 |
 ------

If I query the indexed column x with the primary key, the rows are still returned in the order of x:

mysql> select id, x from test;
 ---- ------ 
| id | x    |
 ---- ------ 
|  3 |    4 |
|  2 |    5 |
|  1 |    6 |
 ---- ------

Why is that? EXPLAIN can show us:

mysql> explain select id, x from test\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: index
possible_keys: NULL
          key: x
      key_len: 5
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using index

The key field of the EXPLAIN shows us that it's reading from the index x. InnoDB indexes always contain the primary key value, so in this case there's no need for MySQL to read anything but the index.

All these internal rules can be difficult to remember. It's a much better idea to use an ORDER BY clause to be explicit if you need the rows to be returned in a particular order. The query engine may still read the rows in whatever order the storage engine chooses, but it will re-sort them if necessary before returning the result set.