MYSQL runns to 100% CPU when triggered a 404

[knuz92]

Describe the Bug

When I call up a non-existent page in Bookstack, e.g., https://demo.bookstackapp.com/trigger404notexistent123123, the CPU goes up to 100%.

Using MariaDB > show full processlist; i discovered the following query as the cause of the error:

select *,viewable_id, viewable_type, SUM(views) as view_count from viewswhere (exists (selectentity_id, entity_type, max(owner_id) as owner_id, max(status) as status from joint_permissionswhereviews.viewable_id=joint_permissions.entity_idandrole_idin (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) andviews.viewable_type=joint_permissions.entity_typegroup byentity_type, entity_id having (status IN (1, 3) or (owner_id = ? and status != 2)))) and (viewable_type!= ? or exists (selectpage_idfromentity_page_datawhereentity_page_data.page_id=views.viewable_idandviews.viewable_type= ? andentity_page_data.draft= ?)) andviewable_typein (?) group byviewable_id, viewable_typeorder byview_count desc limit 10 offset 0

We use a lot of roles and have a larger instance running (4000 pages in 216 books).
The joint_permissions table has 980,000 entries.

Maybe that's causing the error.

I am on the newest Version: BookStack v25.11.6 with a fresh installed Debian 13 Server (the problem existed also on Deb12 with an lower BookStack Version)

Steps to Reproduce

Go to https://demo.bookstackapp.com/trigger404notexistent123123

Expected Behaviour

that the CPU does not go to 100% and performance should be better

Screenshots or Additional Context

Browser Details

No response

Exact BookStack Version

v25.11.6

[knuz92]

The Log from the slow query log file

Time                Id Command  Argument
# Time: 251210 22:21:28
# User@Host: handbuch[handbuch] @ localhost []
# Thread_id: 11123  Schema: handbuch  QC_hit: No
# Query_time: 15.142865  Lock_time: 0.000023  Rows_sent: 10  Rows_examined: 6200373
# Rows_affected: 0  Bytes_sent: 0
use `handbuch`;
SET timestamp=1765401688;
select *, `viewable_id`, `viewable_type`, SUM(views) as view_count from `views` where (exists (select `entity_id`, `entity_type`, max(owner_id) as owner_id, max(status) as status from `joint_permissions` where `views`.`viewable_id` = `joint_permissions`.`entity_id` and `role_id` in (1, 5, 8, 20, 21, 67, 73, 87, 131, 171, 191, 199, 201, 206, 208, 209, 217, 222, 228) and `views`.`viewable_type` = `joint_permissions`.`entity_type` group by `entity_type`, `entity_id` having (status IN (1, 3) or (owner_id = 3 and status != 2)))) and (`viewable_type` != 'page' or exists (select `page_id` from `entity_page_data` where `entity_page_data`.`page_id` = `views`.`viewable_id` and `views`.`viewable_type` = 'page' and `entity_page_data`.`draft` = 0)) and `viewable_type` in ('page') group by `viewable_id`, `viewable_type` order by `view_count` desc limit 10 offset 0;

views.viewable_type has no Index

if i add the index:

CREATE INDEX idx_views_type_id
ON views (viewable_type, viewable_id);

i got the time down to about 7sec