Prepared Statement does not use expected index

I have a very large table of IOT sample that I’m trying to run a relativly simple query against. Running the query normally using the MySql CLI returns a result in ~0.07 seconds. If I first prepare the query either via PDO or by running a SQL PREPARE statement then the request takes over a minute.

I’ve enabled the the optimizer trace feature, and it looks like when the statement is prepared, MySql ignores the index that it should use and does a file sort of the whole table. I’d like any insight if I am doing something wrong or if this looks like a MySql bug.

The table itself contains over 100 million samples, and at least 300 thousand are associated with the device being queried here. I ran these tests with MySql 8.0.23, but when I upgraded to 8.0.25 the issues persisted.

Table definition (some data rows ommited)

Create Table: CREATE TABLE `samples` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `organization_id` int unsigned NOT NULL,
  `device_id` int unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `raw_reading` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `samples_organization_id_foreign` (`organization_id`),
  KEY `samples_reverse_device_id_created_at_organization_id_index` (`device_id`,`created_at` DESC,`organization_id`),
  CONSTRAINT `samples_device_id_foreign` FOREIGN KEY (`device_id`) REFERENCES `devices` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `samples_organization_id_foreign` FOREIGN KEY (`organization_id`) REFERENCES `organizations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=188315314 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Sql That runs in < 1s

select *
from `samples`
where `samples`.`device_id` = 5852
  and `samples`.`device_id` is not null
  and `id` != 188315308
order by `created_at` desc
limit 1;

Sql That runs in over a minute

prepare test_prep from 'select * from `samples` where `samples`.`device_id` = ? and `samples`.`device_id` is not null and `id` != ? order by `created_at` desc limit 1';
set @a = 5852;
set @b = 188315308;
execute test_prep using @a, @b;

Trace for the non prepared SQL can be found at my gist, but the relevant part is

{
  "reconsidering_access_paths_for_index_ordering": {
    "clause": "ORDER BY",
    "steps": [
    ],
    "index_order_summary": {
      "table": "`samples`",
      "index_provides_order": true,
      "order_direction": "asc",
      "index": "samples_reverse_device_id_created_at_organization_id_index",
      "plan_changed": false
    }
  }
},

Trace for the prepared query can be found at my other gist, but the relevant part is

{
  "reconsidering_access_paths_for_index_ordering": {
    "clause": "ORDER BY",
    "steps": [
    ],
    "index_order_summary": {
      "table": "`samples`",
      "index_provides_order": false,
      "order_direction": "undefined",
      "index": "samples_reverse_device_id_created_at_organization_id_index",
      "plan_changed": false
    }
  }
},

Answer

Not full solution, but a workaround. I added an index on just my timestamp and that seems to satisfy the optimizer.

KEY `samples_created_at_index` (`created_at` DESC),

I’m going to try to clean up a minimal test case and post it over on MySql bugs. I’ll add a followup here if anything comes of that.

Advertisement

Answer