Prepared Statement does not use expected index-CodePudding

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
    }
  }
},

CodePudding user response：

The index you want to use is not that bad:

`samples_reverse_device_id_created_at_organization_id_index`
  (`device_id`,`created_at` DESC,`organization_id`)

However, is not a covering index. If the query performance is really important, I would add an index that covers the filtering predicate at least. Your don't need a real covering index since you are retrieving all columns. I would try:

create index ix1 on samples (device_id, created_at, id);

EDIT

Another trick that could promote the index usage is to delay the predicate id != 188315308 as much as possible. If you know that this predicate will be matched by at least one row in the first 100 rows produced by the rest of the predicates you can try rephrasing your query as:

select *
from (
  select *
  from `samples`
  where `samples`.`device_id` = 5852
  order by `created_at` desc
  limit 100
) x
where `id` != 188315308
order by `created_at` desc
limit 1

CodePudding user response：

Get rid of this, since the = 5852 assures that it will be false:

 and `samples`.`device_id` is not null

Then your index, or this one, should work fine.

 INDEX(device_id, created_at, id)

Do not use @variables; the Optimizer seems to not look at the value they contain. That is, instead of

set @a = 5852;
set @b = 188315308;
execute test_prep using @a, @b;

Simply do

execute test_prep using 5852, 188315308;

Consider writing a bug report at bugs.mysql.com

I suspect "order_direction": "undefined" is part of the problem.