Database terms
Where your data lives, breathes, and occasionally panics
Almost every application is, at its core, a system for reading and writing data. This category covers relational and non-relational databases, query optimisation, indexing strategies, transactions, normalisation, replication, and the CAP theorem trade-offs that dictate how distributed data behaves. Choosing and using the right database correctly is one of the highest-leverage skills in backend development.
Database Query Result Streaming PHP 8.0+
Processing large result sets row-by-row without loading the entire dataset into memory - essential for PHP CLI scripts handling millions of rows.
5d ago
database intermediate
INSERT ... ON DUPLICATE KEY UPDATE PHP 5.1+
An atomic MySQL upsert — inserts a new row or updates the existing one if a unique key constraint would be violated.
1mo ago
database intermediate
MySQL EXPLAIN
A MySQL query analysis command showing the execution plan — which indexes are used, how many rows are scanned, and where bottlenecks are.
1mo ago
database intermediate
MySQL FULLTEXT Search
A built-in MySQL search engine supporting natural language and boolean full-text queries on TEXT and VARCHAR columns.
1mo ago
database intermediate
MySQL GROUP BY and Aggregates
GROUP BY collapses rows with matching column values into one — aggregate functions (COUNT, SUM, AVG, MAX, MIN) compute values per group.
1mo ago
database intermediate
MySQL Index Types B-tree, FULLTEXT, SPATIAL, and HASH indexes — each optimised for different query patterns.
1mo ago
database intermediate
MySQL JSON Column Type
A native JSON storage type introduced in MySQL 5.7 that validates, indexes, and queries JSON documents with dedicated functions.
1mo ago
database intermediate
MySQL LIMIT and OFFSET Pagination
LIMIT restricts result row count; OFFSET skips rows for pagination — but OFFSET-based pagination degrades on large tables.
1mo ago
database intermediate
MySQL Query Cache (Deprecated)
A server-side cache for SELECT results removed in MySQL 8.0 — it caused severe scalability issues under concurrent writes.
1mo ago
database intermediate
MySQL Subqueries
A query nested inside another — useful for filtering and deriving values, but correlated subqueries re-execute per outer row and can be O(n²).
1mo ago
database intermediate
N+1 Query Problem PHP 5.1+ A performance anti-pattern where fetching N records triggers N additional queries — one per record — instead of a single JOIN.
1mo ago
database intermediate
Soft Delete Pattern
Marking records as deleted with a deleted_at timestamp instead of physically removing them — preserving data for auditing and recovery.
1mo ago
database intermediate
SQL Window Functions
Window functions (ROW_NUMBER, RANK, LAG, LEAD, SUM OVER) perform calculations across a set of rows related to the current row without collapsing them into a single aggregate — enabling rankings, running totals, and comparisons within result sets.
1mo ago
database intermediate
Database Indexes
Data structures that allow the database engine to find rows matching a condition without scanning every row — the single most impactful performance optimisation available for read-heavy PHP applications.
2mo ago
database intermediate
OLAP vs OLTP
OLTP (Online Transaction Processing) optimises for many small, fast read/write operations — your main application database. OLAP (Online Analytical Processing) optimises for few, large analytical queries scanning millions of rows — your reporting and analytics database.
2mo ago
database intermediate
Query Optimisation
The process of rewriting SQL queries and database structures to reduce execution time — using EXPLAIN to identify full table scans, adding targeted indexes, rewriting JOINs, and eliminating N+1 patterns.
2mo ago
database intermediate
SQLite in Production PHP 5.1+
SQLite is a serverless, file-based SQL database that is appropriate for production use in single-server applications, edge deployments, and embedded systems — with specific limitations around concurrent writes that make it unsuitable for multi-server setups.
2mo ago
database intermediate
Database Replication PHP 5.0+ Copying data from one database server to replicas — synchronous replication guarantees zero data loss, asynchronous is faster but risks losing recent writes on failure.
2mo ago
database intermediate
Database Triggers PHP 5.0+ Stored procedures that fire automatically on INSERT, UPDATE, or DELETE — useful for audit logs and enforcing constraints, but dangerous when they become hidden business logic.
2mo ago
database intermediate
Document Stores PHP 7.0+
Databases that store semi-structured documents (JSON/BSON) — MongoDB, CouchDB — flexible schema, nested data, and horizontal scaling at the cost of no joins and eventual consistency.
2mo ago
database intermediate