Add indexes on resource_link/resource_node for documents

[ywarnier]

Implement recommendations from documentation/optimization.html:

On installations with large numbers of documents per course, the documents list page can become slow due to expensive filtering and sorting queries against the resource_link and resource_node tables. The following two indexes significantly reduce query cost and eliminate the filesort for the default title sort:

-- Composite index covering the most common document-list filter:
-- WHERE c_id = ? AND session_id IS NULL AND group_id IS NULL AND parent_id IS NULL
-- Avoids a full table scan on resource_link when listing documents in a course context.
ALTER TABLE resource_link
    ADD INDEX idx_rl_doc_list (c_id, session_id, group_id, parent_id);

-- Composite index to avoid filesort when listing documents sorted by title
-- within a specific folder (WHERE parent_id = ? ORDER BY title ASC).
ALTER TABLE resource_node
    ADD INDEX idx_rn_parent_title (parent_id, title);
  

These indexes will be added automatically by the Doctrine migrations in the next major release. On existing production systems they can be applied manually with no downtime on MySQL/MariaDB (the ALTER TABLE ... ADD INDEX statement is an online operation in InnoDB).

For the Doctrine schema to reflect these indexes (e.g. so that doctrine:schema:validate passes and future doctrine:migrations:diff does not re-create them), the corresponding #[ORM\Index] attributes must also be added to the entity classes:

// src/CoreBundle/Entity/ResourceLink.php — add alongside the existing #[ORM\Index] attributes:
#[ORM\Index(
    columns: ['c_id', 'session_id', 'group_id', 'parent_id'],
    name: 'idx_rl_doc_list'
)]

// src/CoreBundle/Entity/ResourceNode.php — add a new #[ORM\Index] attribute on the class:
#[ORM\Index(
    columns: ['parent_id', 'title'],
    name: 'idx_rn_parent_title'
)]
  

Once done, remove that section from optimization.html