Optimize function hasBeenAnswered improve performance

🐛 Problem/Motivation
The following slow query was found in the system log:

# Query_time: 3.699115  Lock_time: 0.000155  Rows_sent: 0  Rows_examined: 2,933,790
SELECT "base_table"."result_answer_id" AS "result_answer_id", "base_table"."result_answer_id" AS "base_table_result_answer_id"
FROM "quiz_result_answer" "base_table"
INNER JOIN "quiz_result_answer" "quiz_result_answer"
  ON "quiz_result_answer"."result_answer_id" = "base_table"."result_answer_id"
WHERE "quiz_result_answer"."question_vid" = NULL

This query originates from the method hasBeenAnswered() in the quiz question logic:

public function hasBeenAnswered() {
  $result = \Drupal::entityQuery('quiz_result_answer')
    ->condition('question_vid', $this->getRevisionId())
    ->range(0, 1)
    ->execute();
  return !empty($result);
}

If getRevisionId() returns NULL, Drupal generates the SQL WHERE question_vid = NULL, which is invalid (always false), causes a full table scan, and does not use any index.

DESCRIBE SELECT base_table.result_answer_id AS result_answer_id,
                base_table.result_answer_id AS base_table_result_answer_id
FROM quiz_result_answer base_table
INNER JOIN quiz_result_answer quiz_result_answer
  ON quiz_result_answer.result_answer_id = base_table.result_answer_id
WHERE quiz_result_answer.question_vid = NULL
LIMIT 1 OFFSET 0;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	quiz_result_answer	NULL	ALL	PRIMARY	NULL	NULL	NULL	2,881,570	10.00	Using where
1	SIMPLE	base_table	NULL	eq_ref	PRIMARY	PRIMARY	4	db.quiz_result_answer.result_answer_id	1	100.00	Using index

✅ Proposed resolution
1. Add null check before calling entityQuery
Update hasBeenAnswered() to avoid triggering invalid query when getRevisionId() is NULL.

public function hasBeenAnswered() {
  $revision_id = $this->getRevisionId();
  if (empty($revision_id)) {
    return FALSE;
  }

  $result = \Drupal::entityQuery('quiz_result_answer')
    ->condition('question_vid', $revision_id)
    ->range(0, 1)
    ->execute();

  return !empty($result);
}

2. Add index on question_vid field to improve performance
Since question_vid is not defined via hook_schema() and comes from BaseFieldDefinition, we add index using raw SQL:

/**
 * Adds an index on the question_vid field to improve performance.
 */
function quiz_update_9001() {
  $index_name = 'idx_question_vid';
  $table_name = 'quiz_result_answer';

  // Check if the index already exists.
  $index_exists = \Drupal::database()->query("
    SHOW INDEX FROM {$table_name} WHERE Key_name = :index",
    [':index' => $index_name]
  )->fetchAll();

  if (empty($index_exists)) {
    \Drupal::database()->query("CREATE INDEX {$index_name} ON {$table_name} (question_vid)");
    \Drupal::messenger()->addMessage('Index "idx_question_vid" added to quiz_result_answer table.');
  }
  else {
    \Drupal::messenger()->addMessage('Index "idx_question_vid" already exists.');
  }
}

📈 Expected result

Queries on question_vid will use index → faster performance even on large datasets (~3M rows)

| id | select_type | table              | partitions | type    | possible_keys        | key            | key_len | ref                                       | rows | filtered | Extra        |
| 1  | SIMPLE      | quiz_result_answer | NULL       | ref     | PRIMARY,idx_question_vid | idx_question_vid | 5       | const                                     | 1    | 100.00   | Using index  |
| 1  | SIMPLE      | base_table         | NULL       | eq_ref  | PRIMARY              | PRIMARY        | 4       | db.quiz_result_answer.result_answer_id     | 1    | 100.00   | Using index  |