Missing indexes on timestamp and model_id columns cause full-table scans at scale

Problem/Motivation

hook_schema() defines ai_metering_usage with indexes on (uid, timestamp), (provider_id), (provider_type), and (context_id), but two columns used in frequent queries have no index:

• (timestamp) — used by the period Views filter, CSV/JSON exports, and any query that filters by date range without a uid constraint. With only the composite (uid, timestamp) index, timestamp-only range queries still do a full table scan.
• (model_id) — used by UsageModelFilter::getValueOptions() via SELECT DISTINCT model_id, which currently scans the full table on every log page load.

Steps to reproduce

1. Seed 50,000+ rows in ai_metering_usage.
2. Run EXPLAIN SELECT DISTINCT model_id FROM ai_metering_usage.
3. Observe type = ALL, no key used.
4. Run EXPLAIN SELECT * FROM ai_metering_usage WHERE timestamp BETWEEN 1748736000 AND 1751327999 — same result.

Proposed resolution

Add the two missing indexes to hook_schema() and a hook_update_N() for existing installations:

// In hook_schema(), add to the existing 'indexes' array:
'timestamp' => ['timestamp'],
'model_id'  => ['model_id'],

function ai_metering_update_9012(): void {
  $schema = \Drupal::database()->schema();
  $spec = [
    'fields' => [
      'timestamp' => ['type' => 'int', 'unsigned' => TRUE, 'not null' => TRUE],
      'model_id'  => ['type' => 'varchar', 'length' => 128, 'not null' => TRUE],
    ],
  ];
  if (!$schema->indexExists('ai_metering_usage', 'timestamp')) {
    $schema->addIndex('ai_metering_usage', 'timestamp', ['timestamp'], $spec);
  }
  if (!$schema->indexExists('ai_metering_usage', 'model_id')) {
    $schema->addIndex('ai_metering_usage', 'model_id', ['model_id'], $spec);
  }
}

Remaining tasks

• Add missing indexes to hook_schema()
• Add ai_metering_update_9012() as above
• Verify with EXPLAIN after drush updb

UI / API / Data model changes

Schema change only. No API or UI impact. Existing data preserved.

AI assistance

Analysis and proposed resolution drafted with AI assistance and reviewed by the module maintainer.