Bug: `#__sppagebuilder` has no index for layout lookups - every page, article and module layout lookup is a full table scan

Environment

• SP Page Builder Pro 6.9.1
• Joomla 5.4.9, PHP 8.3, MySQL 8.4
• ~4,000 rows in #__sppagebuilder (mostly article layouts created by the article integration)

The problem

#__sppagebuilder only has a primary key on id. But every layout lookup SP Page Builder performs filters on other columns:

• extension (e.g. com_sppagebuilder, com_content, mod_sppagebuilder)
• extension_view (e.g. page, article, module)
• view_id
• active

For example, the page view loads the layout of the page being displayed this way, and the Articles addon (components/com_sppagebuilder/helpers/articles.php, inside getArticles()) loads the layout of each listed article the same way.

With no index on those columns, MySQL scans the whole table for every lookup. Measured on our site with MySQL's session handler counters, for a single article-layout lookup:

• Before: Handler_read_rnd_next = 4,023 (every row read), EXPLAIN type = ALL, key = NULL
• After adding the index below: Handler_read_rnd_next = 0, Handler_read_next = 1, EXPLAIN type = ref using the new index (1 row)

Because the Articles addon runs one such lookup per listed article (see our separate report on that), a homepage with several Articles blocks triggered tens of full table scans per page view. That query shape was the second-largest consumer of database time on our site (~14–16% in Amazon RDS Performance Insights).

Suggested fix

Add a composite index on (extension, extension_view, view_id) to #__sppagebuilder:

• in the install schema (administrator/components/com_sppagebuilder/sql/install/mysql/install.mysql.utf8.sql), and
• in a new update SQL file, so existing installations get it on their next update.

We added exactly this index to our live site (named idx_ext_view_viewid). It was created online in under 200 ms on ~4,000 rows, with no functional change, and both the page-layout and article-layout lookups now use it. Including active as a fourth column is optional, since the first three already narrow the result to one or two rows.

Impact

Every site using SP Page Builder pays a full table scan for each layout lookup, and the cost grows with the number of articles and pages (the article integration creates a row per article). It's a one-line schema change with no behavioural risk.