Database Optimization

Pragma CMS's database schema is explicitly designed to minimize JOINs, eliminate redundant data, and leverage native MySQL/MariaDB indexing capabilities.

Skinny Table Architecture vs. EAV

Legacy CMS platforms (like WordPress) rely heavily on the EAV (Entity-Attribute-Value) pattern (e.g., wp_postmeta). Fetching a single article with 10 custom fields requires 10 expensive JOIN operations on the same table.

Pragma CMS enforces a Skinny Table / Structured Architecture:

• Dedicated Tables: Core entities (pages, entries, submissions, redirections, …) have their own strictly typed columns. This allows MySQL to store data contiguously on disk, maximizing cache hits at the hardware level.
• JSON Columns for Sparse Data: For modular custom fields or Editor.js blocks, data is stored in native JSON columns. This allows fetching a complex layout in a single database hit, while still enabling JSON_EXTRACT for advanced filtering.

Strategic Composite Indexes

Multilingual CMS platforms often suffer from slow table lookups when resolving translations. Pragma CMS solves this by enforcing Composite Indexes at the schema level.

For example, our translation tables (like entries_translation) utilize a composite primary key on (entry_id, lang_id). When the LanguageManager or EntryManager requests a translated slug, MySQL performs an index-only lookup (O(1) complexity), completely bypassing full table scans.

Hierarchical Data & Recursion

Menus and category trees require fetching hierarchical data. Instead of executing a query for every parent-child relationship (the dreaded N+1 problem), Pragma CMS:

1. Single-Pass Reconstruction: For standard hierarchies, the CMS fetches the entire flat dataset in one query (ORDER BY position ASC) and reconstructs the tree instantly in PHP memory using our reference-based buildTree() algorithm.
2. Recursive CTE Readiness: The database schema is designed to be compatible with Recursive Common Table Expressions (CTEs). This allows for complex, multi-level relationship queries (like finding all descendants of a category) to be executed in a single, high-performance SQL statement on modern MySQL/MariaDB engines.

Explicit Maintenance

To prevent performance degradation due to table fragmentation, Pragma CMS provides a native OPTIMIZE TABLE utility in the Maintenance panel. This command reclaims unused space and reorganizes the physical storage of the InnoDB tables, ensuring that indexes remain compact and fast over years of content updates.