Top 60 MariaDB Interview Questions (2026)

MariaDB is an open-source relational database that speaks SQL and stores data in tables of rows and columns. It was created in 2009 by the original MySQL developers after Oracle acquired MySQL, and it stays largely drop-in compatible with MySQL.

Its appeal is being fully open under the GPL, staying close to MySQL so existing apps and drivers work, while adding its own engines and features like system-versioned tables. It runs from a laptop to large production clusters.

MariaDB began as a fork of MySQL and keeps close binary and protocol compatibility, so most MySQL apps run on it unchanged. Over time the two diverged: MariaDB is fully open under the GPL, ships extra storage engines, and adds some features earlier.

Concrete differences interviewers like to hear: MariaDB's JSON type is an alias for LONGTEXT with functions while MySQL 8 has a native JSON type, they implement window functions and some system functions differently, and MariaDB offers engines like Aria and ColumnStore that MySQL doesn't.

SQL databases like MariaDB store structured data in tables with a fixed schema and use SQL for queries, with strong support for joins and transactions. NoSQL covers document, key-value, wide-column, and graph stores that trade a rigid schema for flexibility or scale.

MariaDB fits relational workloads: related entities, complex queries, and transactions that must stay consistent. NoSQL fits huge volumes, flexible or changing documents, or specific access patterns. Many systems use both.

Use CREATE TABLE with a name, column definitions, data types, and constraints. You set a primary key, mark columns NOT NULL, add defaults, and can pick a storage engine and character set.

AUTO_INCREMENT gives an integer primary key that fills itself in. Choosing the right types up front, integers over strings for ids, DATE over VARCHAR for dates, saves you painful migrations later.

A primary key uniquely identifies each row in a table: its values are unique and never NULL, and a table has exactly one. A foreign key is a column that references a primary key in another table, linking the two and enforcing that the referenced row exists.

Foreign keys give referential integrity: you can't insert a child row pointing at a missing parent, and ON DELETE rules decide what happens when a parent is removed. Both need InnoDB, since MyISAM ignores foreign keys.

INNER JOIN returns rows that match in both tables. LEFT JOIN returns all left-table rows plus matches, filling gaps with NULL; RIGHT JOIN does the mirror. CROSS JOIN pairs every row with every row. MariaDB has no FULL OUTER JOIN keyword, so you emulate it with a UNION of LEFT and RIGHT joins.

WHERE filters individual rows before grouping and can't reference aggregate functions. HAVING filters the groups produced by GROUP BY and can reference aggregates like COUNT or SUM.

So you use WHERE to cut rows early (which is cheaper) and HAVING to keep or drop whole groups based on their aggregate. Putting an aggregate in WHERE is a classic error that raises immediately.

GROUP BY collapses rows that share the same value in the grouped columns into one row per group, and aggregate functions (COUNT, SUM, AVG, MIN, MAX) compute a value across each group.

The rule to state: every column in the SELECT that isn't inside an aggregate should appear in GROUP BY, otherwise the value it returns is undefined. MariaDB is stricter or looser depending on the ONLY_FULL_GROUP_BY setting.

DISTINCT removes duplicate rows from a result set, keeping one copy of each unique combination of the selected columns. It applies to the whole row of selected columns, not just the first one.

It has a cost: MariaDB has to sort or hash the rows to find duplicates, so on large results a well-designed GROUP BY or a schema that prevents duplicates is often better.

NULL means 'unknown', not zero or an empty string. Any arithmetic or comparison with NULL yields NULL, which is why you test it with IS NULL and IS NOT NULL rather than = NULL.

This trips people up in filters and aggregates: COUNT(column) skips NULLs while COUNT(*) counts every row, and functions like COALESCE and IFNULL let you substitute a fallback value when something might be NULL.

INSERT adds new rows, UPDATE changes existing rows that match a condition, and DELETE removes rows that match a condition. UPDATE and DELETE without a WHERE clause hit every row, which is the mistake that ruins tables.

MariaDB also supports INSERT ... ON DUPLICATE KEY UPDATE (upsert) and REPLACE for insert-or-replace logic. Always run the matching SELECT first to confirm which rows your WHERE touches.

DELETE removes rows one at a time based on a WHERE clause, is fully logged, and can roll back. TRUNCATE removes all rows by dropping and recreating the table, is much faster, and resets AUTO_INCREMENT. DROP removes the entire table, structure and all.

Numeric (INT and its sizes, DECIMAL for exact money, FLOAT and DOUBLE for approximate), string (CHAR, VARCHAR, TEXT, BLOB), date and time (DATE, DATETIME, TIMESTAMP, TIME, YEAR), and special types like ENUM, SET, and JSON.

Picking correctly matters: DECIMAL for currency to avoid float rounding, the smallest integer that fits, DATETIME versus TIMESTAMP based on time-zone behavior. Interviewers watch for money stored as FLOAT, which is a red flag.

ORDER BY sorts the result set by one or more columns, ascending by default or descending with DESC. LIMIT caps how many rows come back, and LIMIT with an offset (or LIMIT n OFFSET m) supports pagination.

The pairing is the standard 'top N' pattern. On large tables, offset-based pagination gets slow as the offset grows because MariaDB still scans the skipped rows, which is a good follow-up to raise.

LIKE does pattern matching on strings. The percent sign matches any sequence of characters and the underscore matches exactly one. So 'a%' matches anything starting with a, and '_a%' matches anything with a as the second character.

A leading wildcard ('%term') can't use a normal index, so it forces a scan. For real text search, MariaDB has FULLTEXT indexes, which are the better tool once patterns get complex.

An index is a separate sorted structure, usually a B-tree, that lets MariaDB find rows without scanning the whole table. It's like the index at the back of a book: instead of reading every page, you jump straight to the right one.

Indexes speed up lookups, range queries, joins, and sorting on the indexed columns. The trade-off is extra disk space and slower writes, since every INSERT, UPDATE, and DELETE has to maintain the index too.

A UNIQUE constraint stops duplicate values in a column or set of columns, but unlike a primary key it allows NULLs (and multiple NULLs, since NULL isn't equal to NULL). A table can have many unique constraints but only one primary key.

Both create an index behind the scenes. Use UNIQUE for natural keys like an email address while keeping a separate surrogate primary key like an auto-increment id.

COUNT counts rows, SUM adds numeric values, AVG averages them, MIN and MAX find extremes, and GROUP_CONCAT joins values from a group into one string. They collapse many rows into one summary value.

The gotcha to name: COUNT(*) counts all rows including NULLs, while COUNT(column) counts only non-NULL values in that column. That difference shows up in interview trick questions constantly.

A subquery is a query nested inside another query. It can appear in the WHERE clause (to filter against a computed set), the FROM clause (as a derived table), or the SELECT list (a scalar value per row).

A subquery that references the outer query is a correlated subquery, which runs once per outer row and can be slow. Many subqueries can be rewritten as joins, which the optimizer often handles better.

The mysql or mariadb command-line client connects with a host, port, user, and password, then you pick a database with USE. Applications connect through a driver or connector for their language, giving a host, port, credentials, and database name.

MariaDB listens on port 3306 by default. Connections can use TLS, and users are identified by both a name and the host they connect from, which is why 'user'@'localhost' and 'user'@'%' are different accounts.

A storage engine is the component that actually stores and retrieves table data, and MariaDB lets you pick one per table. InnoDB is the default and handles transactions, row locking, and foreign keys. Others serve specific needs.

Aria is crash-safe and good for read-heavy or temporary tables, MyISAM is the older non-transactional engine, MEMORY keeps tables in RAM, and ColumnStore is a columnar engine for analytics. You set the engine with ENGINE = in CREATE TABLE.

ACID describes the guarantees a transaction gives. Atomicity: all statements succeed or none do. Consistency: the database moves from one valid state to another, respecting constraints. Isolation: concurrent transactions don't corrupt each other's view. Durability: once committed, changes survive a crash.

In MariaDB these guarantees come from InnoDB. MyISAM gives none of them, which is the main reason InnoDB is the default and the right choice for anything where correctness under concurrency matters.

A transaction groups statements so they commit or roll back as a unit. START TRANSACTION begins one, COMMIT makes the changes permanent, and ROLLBACK undoes everything since the start. By default autocommit is on, so each statement is its own transaction until you open one explicitly.

Savepoints let you roll back part of a transaction. Transactions need a transactional engine (InnoDB); running them on MyISAM silently does nothing because MyISAM ignores them.

Four levels trade consistency against concurrency. READ UNCOMMITTED allows dirty reads. READ COMMITTED prevents dirty reads but allows non-repeatable reads. REPEATABLE READ (InnoDB's default) prevents non-repeatable reads using a consistent snapshot. SERIALIZABLE prevents everything by making reads behave like locking.

Each level permits certain anomalies: dirty reads, non-repeatable reads, and phantom reads. Naming which anomaly each level allows is what turns a memorized list into a real answer.

The common ones: the primary key (a clustered index in InnoDB, so the table is physically ordered by it), secondary B-tree indexes on other columns, UNIQUE indexes that also enforce no duplicates, composite indexes over several columns, and FULLTEXT indexes for text search.

MariaDB also has spatial indexes for geometry data and, on some engines, hash indexes. Choosing the right type and column order is most of query tuning.

A clustered index stores the actual row data in the order of the index key, so the index and the table are the same structure. In InnoDB the primary key is the clustered index, which is why primary-key lookups are fast: they land directly on the row.

Secondary indexes in InnoDB store the primary key value, not a row pointer, so a secondary index lookup does two steps: find the primary key, then fetch the row. That's why a short primary key keeps every secondary index smaller.

A composite index is sorted by its first column, then the second within that, and so on, like a phone book sorted by last name then first name. MariaDB can use the index for a query only if the query filters on a leftmost prefix of the columns.

So an index on (a, b, c) helps queries filtering on a, or a and b, or all three, but not one filtering on b alone. This 'leftmost prefix' rule decides how you order columns: put the most selective and most commonly filtered column first.

EXPLAIN shows the plan MariaDB will use: which index (if any) each table uses, the join order, the estimated rows scanned, and the access type. You read it to catch full table scans, missing indexes, and expensive sorts.

Key columns: type (aim for ref or range, avoid ALL which means a full scan), key (the index chosen), rows (the estimate), and Extra (watch for 'Using filesort' and 'Using temporary'). EXPLAIN ANALYZE runs the query and shows real timings.

Normalization organizes tables to reduce redundancy and update anomalies. First normal form: atomic values, no repeating groups. Second normal form: no partial dependency on part of a composite key. Third normal form: no transitive dependency, non-key columns depend only on the key.

The practical goal is that each fact lives in one place. You then denormalize selectively for read performance, and being able to explain that trade-off both ways is the sign of experience.

A view is a saved query that behaves like a virtual table: selecting from it runs the underlying query. It doesn't store data (unless it's a materialized pattern you build yourself), so it always reflects current rows.

Views simplify complex joins into a reusable name, hide columns for security, and give a stable interface while the tables underneath change. Some views are updatable, but joins and aggregates usually make them read-only.

A stored procedure is a named block of SQL stored in the database that you call with CALL; it can take parameters, run multiple statements, and control flow with loops and conditionals. A stored function returns a single value and can be used inside a query.

They cut round trips and centralize logic, but they can hide business rules from application code and are harder to version and test. Teams weigh that trade-off; interviewers like hearing you know both sides.

A trigger is SQL that fires automatically before or after an INSERT, UPDATE, or DELETE on a table. Common uses are auditing changes, keeping a denormalized value in sync, or enforcing a rule that a constraint can't express.

The risk is hidden behavior: triggers run invisibly, can slow every write, and make debugging harder because effects happen without an explicit call. Use them sparingly and document them, because the next engineer won't expect them.

AUTO_INCREMENT gives each new row the next integer automatically, usually on the primary key. The counter only goes up; deleting rows doesn't reuse their ids, and a rolled-back INSERT still consumes the value it grabbed, so gaps are normal.

Edge cases interviewers probe: the counter resets differently after a restart depending on engine, TRUNCATE resets it to the start, and in replication or clusters you set auto_increment_increment and offset so nodes don't collide.

A character set defines which characters can be stored and how they're encoded; utf8mb4 is the modern default because it covers all Unicode including emoji. A collation defines the rules for comparing and sorting those characters, including case and accent sensitivity.

They matter for correctness: the wrong collation makes 'A' and 'a' sort or compare unexpectedly, and mixing collations in a join can block index use or raise an 'illegal mix of collations' error. Note MariaDB's utf8 historically meant a three-byte subset, so prefer utf8mb4 explicitly.

mariadb-dump (the mysqldump equivalent) produces a logical backup: a text file of SQL statements that recreates the schema and data. It's portable and simple but slow to restore on large databases and locks or snapshots to stay consistent.

For big or busy systems, Mariabackup takes a physical, near-hot backup of the data files with little locking. The interview-grade answer pairs a backup method with the recovery goal: how fast you need to restore and how much data loss you can tolerate.

Both stack the rows of two queries that have matching columns. UNION removes duplicate rows across the combined result, which means an extra sort or hash step. UNION ALL keeps every row, duplicates included, and is faster because it skips that step.

So use UNION ALL by default when you know there are no duplicates or you want them all, and reserve UNION for when de-duplication is actually needed. Picking UNION ALL for speed when duplicates can't happen is the answer interviewers like.

A prepared statement sends a query template with placeholders once, then executes it repeatedly with different parameter values. The server parses and plans the template a single time, and parameters are sent separately from the SQL text.

Two wins: performance from reusing the plan on repeated calls, and security, because parameters can't break out of their placeholder, which is the standard defense against SQL injection. Application connectors use them under the hood.

SQL injection is when user input is concatenated into a query so an attacker can change its meaning, for example ending a string early and appending their own SQL. It can read, alter, or destroy data.

The fix is parameterized queries (prepared statements) so input is always data, never executable SQL. Add least-privilege database accounts, input validation, and avoiding dynamic SQL built from strings. Escaping by hand is error-prone and not the primary defense.

A temporary table exists only for the current session and disappears when the session ends. You create it with CREATE TEMPORARY TABLE, and it's invisible to other connections, so two sessions can have same-named temp tables.

They help when you need to stage intermediate results across several steps of a complex report or migration. The caution: MariaDB may spill large temp tables to disk, which shows up as 'Using temporary' in EXPLAIN and can slow a query.

A self join joins a table to itself, using table aliases to treat the two references as separate tables. You need it when rows relate to other rows in the same table, the classic case being an employees table where each row has a manager_id pointing at another employee.

A primary server writes changes to its binary log, and replicas read that log and replay the events to stay in sync. MariaDB supports asynchronous replication by default (the primary doesn't wait for replicas) and semi-synchronous (it waits for at least one replica to acknowledge).

It's used for read scaling (route reads to replicas), high availability (promote a replica if the primary fails), and backups off a replica. The trade-off with async replication is replica lag: a replica can fall behind, so a read there may be slightly stale.

Galera is a synchronous multi-primary cluster built into MariaDB. Every node can accept writes, and a transaction isn't committed until it's certified across all nodes, so all nodes stay consistent with no replica lag.

Compared to classic primary-replica replication, Galera gives synchronous consistency and any-node writes, but writes cost more because of the certification round trip, and a conflicting write can be rolled back at commit. It suits high-availability setups that need consistent reads from every node.

A deadlock happens when two transactions each hold a lock the other needs, so neither can proceed. InnoDB detects this and kills one transaction with an error, letting the other continue. The killed transaction should retry.

You reduce them by accessing rows in a consistent order across transactions, keeping transactions short, using appropriate indexes so locks are narrow, and lowering isolation where safe. The application should catch the deadlock error and retry, because you can't eliminate them entirely.

InnoDB combines two mechanisms. Multi-version concurrency control (MVCC) gives each transaction a consistent snapshot for reads, so readers don't block writers and writers don't block readers. Locking (row locks, next-key locks) handles writes and prevents conflicting changes.

This is why a plain SELECT under REPEATABLE READ sees a snapshot without taking row locks, while SELECT ... FOR UPDATE takes locks to reserve rows for a coming write. Understanding when a statement reads a snapshot versus takes a lock is central to diagnosing contention.

The optimizer is cost-based: it estimates the cost of alternative plans (join orders, which index to use, whether to scan) using statistics about table sizes, index selectivity, and value distribution, then picks the cheapest estimate.

When it chooses badly, the usual culprit is stale statistics, so ANALYZE TABLE refreshes them. MariaDB also supports engine-independent statistics and histograms for skewed columns, and optimizer hints or STRAIGHT_JOIN to override join order when you know better.

Partitioning splits one logical table into physical pieces by a key, such as a date range or a hash of an id. Queries that filter on the partition key can skip whole partitions (partition pruning), and dropping old data becomes an instant DROP PARTITION instead of a slow DELETE.

It helps large tables with a clear time or key dimension, like logs partitioned by month. It doesn't magically speed up queries that don't filter on the partition key, and it adds operational complexity, so it's a targeted tool, not a default.

Start by confirming it's the query: check the slow query log and current sessions with SHOW PROCESSLIST to see what's running and blocking. Then EXPLAIN the query to see if it's doing a full scan, a filesort, or a bad join order, and compare against when it was fast.

Common causes: a missing or dropped index, stale statistics after data growth, a change in data volume or distribution, lock contention, or a parameter that now hits a different plan. Fix at the right layer (add an index, ANALYZE TABLE, rewrite the query) and confirm with EXPLAIN and timing.

Several reasons. The query might filter on a column that isn't a leftmost prefix of the index. A function or type mismatch on the column (WHERE YEAR(created) = 2025 or comparing a string column to a number) stops the index being used. Or the optimizer decides a scan is cheaper because the query returns most of the table.

Also check for stale statistics (run ANALYZE TABLE), a leading-wildcard LIKE, an OR across different columns, or a collation mismatch in a join. EXPLAIN plus knowing these patterns turns this from a mystery into a checklist.

Opening a MariaDB connection is expensive, so applications use a connection pool: a fixed set of reusable connections handed out and returned, sized to the workload rather than one per request. MariaDB also has a thread pool plugin to handle many connections without one thread each.

Sizing matters: too few pooled connections and requests queue, too many and the server thrashes on context switches and memory. Set max_connections with headroom, watch for connection leaks (borrowed but never returned), and put a proxy like MaxScale in front for routing and pooling across nodes.

System-versioned (temporal) tables automatically keep the history of every row. When you update or delete a row, MariaDB retains the old version with the time range it was valid. You query the past with FOR SYSTEM_TIME clauses to see the data as of any point in time.

This is a MariaDB feature that MySQL doesn't have natively. It's useful for audit trails, point-in-time reporting, and 'what did this record look like last month' questions, without writing your own history tables and triggers.

A window function computes a value across a set of rows related to the current row (the window) without collapsing them, so you keep every row and add a computed column. GROUP BY collapses rows into one per group; window functions don't.

They power running totals, rankings (ROW_NUMBER, RANK, DENSE_RANK), and per-group comparisons like each row's salary versus its department average. MariaDB has supported them since 10.2. The OVER clause with PARTITION BY and ORDER BY defines the window.

A common table expression (CTE) is a named temporary result defined with WITH at the start of a query, which makes complex queries readable by naming intermediate steps instead of nesting subqueries. A recursive CTE references itself to walk hierarchies or generate sequences.

Recursive CTEs are the clean way to query tree data like an org chart or a category tree: a base case plus a recursive part that joins back to the CTE until it stops. MariaDB has supported them since 10.2.

The buffer pool is InnoDB's in-memory cache of table and index pages. Reads and writes go through it, so a query whose data is already cached avoids disk entirely. It's the single most impactful memory setting for a transactional MariaDB server.

The guidance is to size innodb_buffer_pool_size to hold the working set, often a large share of a dedicated server's RAM, so hot data stays resident. Too small and you thrash to disk; you monitor the buffer pool hit rate to judge whether it's big enough.

Database-enforced foreign keys guarantee referential integrity no matter which application or script writes the data, and ON DELETE and ON UPDATE rules automate cleanup. That safety is real and hard to replicate reliably in application code.

The costs: foreign key checks add write overhead, complicate bulk loads and some migrations, and can cause lock contention on the parent table. High-write systems sometimes drop them and enforce integrity in the application, which is a deliberate trade you should be able to argue both ways.

A generated column's value is derived from other columns by an expression rather than being inserted directly. VIRTUAL columns compute on read and store nothing; STORED columns compute on write and persist the value, so they take space but can be indexed.

They keep derived data consistent (a full_name from first and last, or a JSON field pulled into a real column) without application code remembering to set it. Indexing a STORED generated column is a common way to index an expression or a JSON path.

MariaDB's JSON type is an alias for LONGTEXT with a validity check and a set of JSON functions (JSON_EXTRACT, JSON_VALUE, JSON_SET, and the shorthand column->'$.path'). This differs from MySQL 8, which stores JSON in a native binary format.

Because it's stored as text, you often pull a JSON field into a generated column and index that for fast lookups, rather than expecting to index inside the JSON directly. For heavy JSON workloads, that's a real limitation worth naming versus MySQL or PostgreSQL's JSONB.

MariaDB supports online DDL (ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE) for many changes, letting reads and writes continue while the change runs. For changes that can't run in-place, or on very large tables, teams use tools like pt-online-schema-change or gh-ost that build a shadow table and swap it in.

The plan also matters: test on a replica first, do it in a low-traffic window, watch replication lag, and have a rollback. A blocking ALTER on a huge hot table is how you cause an outage, so the technical answer is as much process as syntax.

The standard path is primary-replica replication with read-write splitting: writes go to the primary, reads spread across replicas, often through a proxy like MaxScale that routes automatically. Caching hot results in something like Redis takes load off the database entirely.

The catch to acknowledge is replica lag: a read right after a write might not see it, so reads that must be immediately consistent go to the primary. Beyond replicas, options are sharding by key or moving analytics to ColumnStore, each with its own complexity cost.

Watch queries per second and slow queries, the InnoDB buffer pool hit rate, connections in use versus max_connections, replication lag, lock waits and deadlocks, and disk and CPU. SHOW GLOBAL STATUS and the information_schema and performance_schema expose most of these.

In production you feed those into a metrics stack (Prometheus with the mysqld exporter, Grafana dashboards) plus the slow query log for offending queries. The point is trend and alert, not check by hand, so you catch a rising buffer-pool miss rate or replica lag before users do.

Clarify the goals first: acceptable downtime, data-loss tolerance, and read versus write scale. Then pick an architecture. For automatic failover with consistent reads, a Galera cluster across three nodes plus a proxy is a common choice. For read scaling with simpler writes, a primary with replicas and an orchestrator that promotes a replica on failure.

Round it out with the operational edges: a load balancer or MaxScale for routing, health checks and automated failover, backups plus tested restores, monitoring and alerting, and multi-zone placement so one datacenter failure doesn't take everything. Structuring the answer, goals, topology, failover, backups, monitoring, is what's really being evaluated.