-1

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.

Maurice
  • 6,698
  • 9
  • 47
  • 104
  • Possible duplicate of https://stackoverflow.com/questions/49815703/what-is-the-default-order-by-for-a-mysql-innodb-query-that-omits-the-order-by/49826908#49826908 – Bill Karwin Aug 18 '22 at 02:00
  • Have you tried `SELECT cropvarietyname.id FROM \`seedcalendar-test\`.cropvarietyname where id ='1' LIMIT 1;` – 新Acesyyy Aug 18 '22 at 02:01
  • @新Acesyyy please... – Maurice Aug 18 '22 at 02:02
  • 1
    Limit without order by is almost always a mistake. The server can feel free to return any arbitrary row. If you want some rows in preference to others, specify an appropriate order by. – ysth Aug 18 '22 at 05:08
  • @ysth when one or more fields have no index the database will always return the first row. – Maurice Aug 18 '22 at 18:23

3 Answers3

2

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.

ysth
  • 96,171
  • 6
  • 121
  • 214
0

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Another rule; this one makes it really hard to predict! If a 'covering' index will suffice, it _may_ be used. This can lead to seemingly random ordering of rows. Bottom line: Use `ORDER BY`. – Rick James Aug 19 '22 at 16:43
0

Bottom line: Always use ORDER BY to get predictability.

Your 4 cases (plus 1), and assuming PRIMARY KEY (id) and in_no_index does not show up in any INDEX):

SELECT id ...  WHERE id=1    LIMIT 1  -- 1
SELECT id ...                LIMIT 1  -- 2
SELECT in_no_index       ... LIMIT 1  -- 3
SELECT in_no_index,   id ... LIMIT 1  -- 4
SELECT in_some_index, id ... LIMIT 1  -- 5

1: Since id is UNIQUE (because of being the PK), the LIMIT is irrelevant; the one row with id=1 is delivered.

2,5: (Surprise!) In InnoDB, secondary keys implicitly include a copy of the PK. Hence any index can be used. The Optimizer may pick the "smallest" index. You get a rather random result.

3,4: The Optimizer has no reasonable choice other than to scan the table. Hence, it delivers the column for the "first" row. Since InnoDB always orders the data BTree by the PK, that will be the row with the smallest id.

Further note: Using ORDER BY does not necessarily take any extra effort. It depends on whether the Optimizer can eliminate the sorting when it is using an INDEX.

Rick James
  • 135,179
  • 13
  • 127
  • 222