MySQL Interview Questions

SQL is the query language itself, the standard syntax used to interact with relational databases. MySQL is a specific database management system that implements SQL, along with its own extensions and specific behavior. Other systems like PostgreSQL or SQL Server also implement SQL, each with its own particular dialect.

InnoDB and MyISAM are the two most common. InnoDB supports transactions, foreign keys, and row-level locking, and is the default engine since MySQL 5.5. MyISAM is simpler and historically faster for read-heavy workloads, but it doesn't support transactions or foreign keys and uses table-level locking.

A primary key uniquely identifies each row in a table. It can't contain NULL values and must be unique across all rows. MySQL automatically creates an index on the primary key, which makes lookups by that key fast.

A foreign key is a column, or set of columns, in one table that references the primary key of another table, enforcing a link between the two. It helps maintain referential integrity, preventing a row from referencing a record that doesn't actually exist in the related table.

CHAR is a fixed-length string type, always storing the declared number of characters and padding shorter values with spaces. VARCHAR is a variable-length type, storing only the actual characters plus a small length prefix, which usually saves space for values that vary in length.

An index is a data structure, typically a B-tree, that lets MySQL find rows matching a condition without scanning the entire table. It matters because a query on an indexed column can be dramatically faster than one on an unindexed column, especially as a table grows large.

Both enforce uniqueness across a column or set of columns, but a table can have only one PRIMARY KEY while it can have multiple UNIQUE constraints. A PRIMARY KEY also can't contain NULL values, while a UNIQUE constraint can allow a single NULL value depending on the column definition.

AUTO_INCREMENT automatically generates a unique, incrementing numeric value for a column whenever a new row is inserted without specifying that column's value. It's commonly used on primary key columns to generate simple, sequential IDs without the application needing to manage them itself.

Numeric types include INT, BIGINT, DECIMAL, and FLOAT. String types include CHAR, VARCHAR, and TEXT. Date and time types include DATE, DATETIME, and TIMESTAMP. Choosing the right type matters for both storage efficiency and making sure the database enforces valid data.

DELETE removes specific rows based on a condition and can be rolled back within a transaction. TRUNCATE removes all rows from a table at once, is faster than DELETE, but generally can't be rolled back and resets AUTO_INCREMENT. DROP removes the entire table structure along with its data, permanently.

A view is a saved SQL query that behaves like a virtual table, letting you reference it like a regular table without storing the underlying data separately. It's useful for simplifying a complex, frequently used query or restricting which columns a user can see.

WHERE filters individual rows before any grouping happens, and it can't reference aggregate functions like COUNT or SUM. HAVING filters groups after a GROUP BY has already been applied, and it can reference aggregate functions, since it operates on the grouped results.

Normalization organizes tables to reduce data redundancy and avoid update anomalies, typically by splitting data into related tables connected through foreign keys rather than repeating the same information across many rows. It matters because a poorly normalized schema makes updates error-prone and wastes storage.

Denormalization deliberately introduces some redundancy back into a schema, often by combining data from related tables into one, to reduce the number of joins a common query needs. It's typically used when read performance matters more than storage efficiency or strict data consistency for a specific, frequently run query.

LIMIT restricts the number of rows a query returns, commonly used for pagination, like showing 20 results per page. It's often combined with OFFSET to skip a certain number of rows before starting to return results.

INNER JOIN returns only rows that have matching values in both tables being joined. LEFT JOIN returns all rows from the left table, along with matching rows from the right table, filling in NULL for columns from the right table when there's no match.

A transaction groups multiple SQL statements into a single unit of work, where either all of them succeed and get committed together, or none of them take effect if something fails and the transaction is rolled back. It's essential for operations that need to stay consistent, like transferring money between two accounts.

ACID stands for Atomicity, Consistency, Isolation, and Durability, the four properties that guarantee a transaction behaves reliably. Atomicity means a transaction fully succeeds or fully fails, consistency means it leaves the database in a valid state, isolation means concurrent transactions don't interfere with each other, and durability means a committed transaction survives a crash.

mysqldump is a command-line utility for creating a backup of a MySQL database, exporting its structure and data as a set of SQL statements that can recreate the database later. It's one of the most common ways to back up or migrate a MySQL database, especially for smaller datasets.

MySQL runs on port 3306 by default. This can be changed in the MySQL configuration file, but 3306 is the standard port most client tools and applications expect unless explicitly configured otherwise.

You'd use CREATE DATABASE followed by the database name, then CREATE TABLE with the table name and a list of column definitions, each specifying a name, data type, and any constraints. For example: CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100));

DATETIME stores a date and time value without any timezone awareness, and its valid range spans a much wider period. TIMESTAMP stores the value relative to UTC and converts it based on the connection's timezone setting, but it has a narrower valid range and is typically used for tracking record creation or update times.

GROUP BY groups rows sharing the same value in specified columns, typically used together with an aggregate function like COUNT, SUM, or AVG to calculate a summary value per group. For example, grouping orders by customer to calculate each customer's total order value.

NULL represents the absence of any value, meaning unknown or not applicable, and it's distinct from an empty string or zero, which are actual, defined values. Comparisons with NULL require special handling, using IS NULL or IS NOT NULL, since NULL doesn't equal anything, including itself, under standard comparison operators.

ORDER BY sorts a query's result set based on one or more columns, either ascending (the default) or descending. It's commonly combined with LIMIT to retrieve, for example, the ten most recent records from a table.

A composite key is a primary key made up of two or more columns together, where the combination of values across those columns must be unique, even if individual columns alone might repeat. It's used when no single column can reliably identify a row uniquely on its own.

In InnoDB, the primary key acts as a clustered index, meaning the actual table data is physically stored in primary key order. Any other index is a secondary, non-clustered index, which stores the indexed column's value alongside a pointer back to the primary key, requiring an extra lookup to fetch the full row.

DISTINCT removes duplicate rows from a query's result set, keeping only unique combinations of the selected columns. It's useful when you want a list of unique values, like distinct customer countries, without repeats.

A local variable is declared inside a stored procedure or function and only exists within that routine's scope. A session variable, prefixed with @, persists for the duration of a client's connection and can be set and read across multiple statements within that same session.

EXPLAIN shows how MySQL plans to execute a given query, including which indexes it will use, the join order, and an estimate of how many rows it expects to examine. It's the starting point for understanding why a query might be slow and how to optimize it.

MySQL supports character sets like utf8mb4 for full Unicode support, along with older ones like latin1. Collation determines how string comparison and sorting work for a given character set, like whether comparisons are case-sensitive. Choosing utf8mb4 generally matters for correctly storing emoji and characters outside the basic multilingual plane.

A trigger is a stored routine that automatically runs in response to a specific event, like an INSERT, UPDATE, or DELETE, on a particular table. It's often used to automatically maintain an audit log or enforce a business rule that can't easily be expressed through a simple constraint.

A stored procedure performs an action and can return multiple values or none at all, and it's called using the CALL statement. A function always returns exactly one value and can be used directly within a SQL expression, like inside a SELECT statement.

You'd use ALTER TABLE with RENAME TO to rename a table, or ALTER TABLE with CHANGE or RENAME COLUMN to rename a column, depending on the MySQL version. For example: ALTER TABLE old_name RENAME TO new_name;

MySQL is a relational database, storing data in structured tables with a fixed schema and strong support for complex joins and transactions. MongoDB is a document database, storing flexible, JSON-like documents without requiring a fixed schema upfront, which suits data that varies in shape or evolves quickly.

InnoDB uses row-level locking, meaning a transaction only locks the specific rows it's actually modifying, which allows much better concurrency for write-heavy workloads. MyISAM uses table-level locking, meaning any write locks the entire table, which becomes a bottleneck as concurrent write activity increases.

Replication copies data changes from a source (primary) server to one or more replica servers, typically by streaming the source's binary log to the replicas, which then apply the same changes. Common types include asynchronous replication, where replicas can lag slightly behind, and semi-synchronous replication, where the source waits for at least one replica to acknowledge a change before committing.

A covering index includes all the columns a query needs, both for filtering and for the columns being selected, so MySQL can satisfy the entire query from the index alone without needing to look up the full row in the table. It's useful because it avoids an extra read step, making the query noticeably faster.

I'd check the SHOW ENGINE INNODB STATUS output, which reports the most recent deadlock, including which transactions and rows were involved. Fixing it usually means making sure transactions access tables and rows in a consistent order across the application, or shortening transactions so they hold locks for less time.

The buffer pool caches table and index data in memory, reducing how often MySQL needs to read from disk. Sizing it usually means setting it as large as possible without starving the operating system or other processes, often around 60 to 80 percent of available RAM on a dedicated database server.

For smaller changes, MySQL's online DDL support can add or modify columns and indexes without locking the table for reads and writes. For larger, riskier changes, tools like gh-ost or pt-online-schema-change create a copy of the table, apply the change there, and swap it in, minimizing the window of actual disruption.

Pessimistic locking acquires a lock on a row before modifying it, blocking other transactions from touching that row until the lock is released, typically using SELECT ... FOR UPDATE. Optimistic locking doesn't lock upfront, instead checking a version number or timestamp column at update time to detect whether another transaction changed the row first, and failing the update if so.

I'd enable it in the configuration, setting long_query_time to define what counts as slow, then review the resulting log with a tool like mysqldumpslow or pt-query-digest to identify which queries run most frequently or take the longest, prioritizing those for optimization.

A B-tree index, MySQL's default index type, works well for equality and range lookups on structured column values. A full-text index is built specifically for searching text content for words or phrases, supporting relevance-based matching that a regular B-tree index isn't designed for.

The query cache stored the exact result of a SELECT query, returning the cached result instantly if the identical query ran again before any underlying table changed. It was removed because it scaled poorly under concurrent writes, since any write to a table invalidated all cached queries referencing it, creating a bottleneck that outweighed its benefit on busy systems.

Partitioning splits a large table into smaller physical pieces based on a defined rule, like a range of dates, while still letting it be queried as a single logical table. It's useful for very large tables where queries commonly filter on the partitioning column, since MySQL can skip scanning partitions that don't match.

I'd use parameterized queries or prepared statements exclusively, never building SQL by directly concatenating user input into a query string. Most MySQL client libraries support this natively, and it's a much more reliable defense than trying to manually sanitize or escape input.

A read replica is used to offload read-only queries from the primary server, spreading read traffic across multiple servers to improve overall throughput. A replica used for high availability exists mainly as a standby that can be promoted to primary if the original primary fails, prioritizing failover readiness over serving live read traffic.

I'd analyze the actual query patterns, prioritizing indexes for the most frequent and performance-sensitive queries rather than indexing every column that's ever filtered on. Composite indexes covering the most common combinations of filter columns are often more effective than many separate single-column indexes.

The binary log records every change made to the database in the order it happened, and beyond powering replication, it's also used for point-in-time recovery, letting you restore a backup and then replay changes up to a specific moment before a failure occurred.

I'd use ALTER TABLE table_name ENGINE=InnoDB, though for a large table in production I'd typically do this during a low-traffic window or use an online schema change tool, since converting a large table can take a while and briefly affect performance.

A self-referencing foreign key is a column in a table that references the primary key of that same table, commonly used to model hierarchical data, like an employee table where a manager_id column references another row's id in the same employees table.

I'd use a connection pool, either built into the application framework or a separate tool like ProxySQL, to reuse existing database connections rather than opening a new one for every request, since establishing a new MySQL connection has real overhead. Sizing the pool appropriately for expected concurrency avoids both connection exhaustion and wasted idle connections.

READ COMMITTED means a transaction only sees data that was committed at the moment each individual query runs, so repeated reads within the same transaction can see different results if another transaction commits in between. REPEATABLE READ, InnoDB's default, guarantees that repeated reads within the same transaction see a consistent snapshot, even if other transactions commit changes during that time.

I'd use a tool like Percona XtraBackup, which performs a physical backup of InnoDB tables without requiring a full table lock, unlike a logical backup with mysqldump on a busy system. For very large databases, backing up from a dedicated replica rather than the primary avoids adding backup load to the server actually serving production traffic.

Global privileges apply across the entire MySQL server, database-level privileges apply to all objects within a specific database, and table-level privileges apply to a single specific table. Granting the narrowest privilege scope that a given application or user actually needs follows the principle of least privilege and limits the damage from a compromised credential.

I'd check SHOW PROCESSLIST to see what's currently connected and what each connection is doing, looking for connections stuck in a sleeping or long-running state that indicate the application isn't closing connections properly or a query is hanging. I'd also review the max_connections setting and whether connection pooling is actually being used correctly on the application side.

UNION combines the result sets of two or more SELECT queries and removes duplicate rows from the combined result. UNION ALL does the same combination but keeps all rows, including duplicates, which makes it faster since MySQL doesn't need to do the extra work of checking for and removing duplicates.

Sharding splits data across multiple database instances based on a shard key, like customer ID or region, so no single instance holds the entire dataset. It introduces real challenges around cross-shard queries and joins, which become much harder, maintaining consistent schema changes across every shard, and rebalancing data if one shard grows disproportionately larger than others.

I'd typically use a combination of semi-synchronous replication to minimize data loss on failover, paired with an orchestration tool like MySQL InnoDB Cluster, Orchestrator, or a cloud provider's managed failover mechanism, to detect a primary failure and automatically promote a replica. I'd also make sure the application layer can reconnect cleanly to the new primary without manual intervention.

I'd project growth in both data volume and query load based on current trends and known upcoming business drivers, then stress test the current setup against that projected load to find where it actually breaks first, whether that's CPU, memory, disk I/O, or connection limits. Planning for vertical scaling limits and when horizontal approaches like read replicas or sharding will become necessary keeps the team ahead of the growth rather than reacting to an outage.

Vertical scaling means upgrading a single server's resources, more CPU, memory, or faster storage, which is simpler but eventually hits a hardware ceiling and doesn't help with a single point of failure. Horizontal scaling means adding more servers, through read replicas or sharding, which scales further but adds real architectural complexity, so I'd generally push vertical scaling as far as practical before taking on horizontal complexity.

I'd check whether the replica is under-resourced relative to the write volume it needs to replay, whether a single long-running query on the replica is blocking replication threads, or whether the replication itself is single-threaded on an older MySQL version and can't keep pace with parallel writes on the primary. Depending on the cause, the fix ranges from enabling multi-threaded replication, to scaling up the replica, to reducing write volume on the primary.

I'd enforce strong authentication, disable remote root login, restrict network access through firewall rules or security groups so only application servers can reach the database port, and encrypt connections with TLS. Regularly auditing user privileges to remove unnecessary access, and rotating credentials, rounds out a reasonably solid security posture beyond just the initial setup.

I'd look at concrete signals: whether write throughput is consistently approaching the primary's practical ceiling, whether query latency is degrading under current load even after optimization, and whether a single point of failure is now an unacceptable business risk given the application's scale. I'd want clear, measured evidence rather than moving to a more complex architecture preemptively based on hypothetical future growth.

I'd track key metrics like query latency, replication lag, connection counts, buffer pool hit ratio, and disk I/O, with alerting thresholds tuned to the specific system's normal baseline rather than generic defaults. Tools like Percona Monitoring and Management or a cloud provider's built-in database monitoring give visibility into trends before they become outages.

I'd use an online schema change tool like gh-ost or pt-online-schema-change, which builds a shadow copy of the table with the new schema, copies data over in small batches while tracking ongoing changes, and then swaps the tables atomically at the end. This keeps the table available for reads and writes throughout almost the entire process, unlike a direct ALTER TABLE on a huge table.

I'd define clear recovery point and recovery time objectives based on what the business can actually tolerate, then build backup and replication strategies around those targets, like frequent incremental backups plus a geographically separate replica for regional failure scenarios. Regularly testing the actual recovery process, beyond just assuming backups will work when needed, is the part teams most often skip until it's too late.

I'd weigh the team's operational expertise and available time for database administration against the cost premium a managed service charges for handling backups, patching, and failover automatically. For a smaller team without dedicated database expertise, a managed service usually pays for itself in reduced operational risk, while a team with deep MySQL expertise and very specific tuning needs might get more value from self-managing.

I'd establish shared standards for the things that genuinely affect reliability and risk, backup and recovery procedures, schema change review processes, security and access control baselines, while giving teams flexibility in their specific schema design and query patterns for their own domain. Centralizing tooling for backups, monitoring, and schema migrations reduces duplicated effort across teams without over-constraining their day-to-day work.

I'd document the growing operational burden currently falling on individual product teams, incident frequency and time spent on database-related firefighting, inconsistent backup and monitoring practices across teams, and project how a dedicated team's shared expertise and tooling would reduce that burden and risk at scale. Framing it around risk reduction and engineering time saved, beyond just infrastructure cost, makes the investment case land with non-technical leadership.

I'd push hard on alternatives first, better indexing, caching, read replicas, and vertical scaling, since sharding's complexity cost is genuinely high and affects every future feature built on that data. I'd only move to sharding once there's clear, sustained evidence that write throughput has hit a ceiling that no other approach can practically solve, and I'd want the sharding strategy itself carefully designed before implementation, since retrofitting it after the fact is much harder than designing for it upfront.

I'd start from the business's actual recovery time and recovery point requirements, which are often never explicitly stated, and work backward to whether current backup frequency, replication topology, and tested recovery procedures genuinely meet them. A disaster recovery plan that's never been tested under realistic failure conditions isn't a real plan, so I'd prioritize running genuine failover drills over just documenting a theoretical process.

I'd have them work directly on real production incidents and performance investigations rather than only reading about concepts abstractly, pairing them through diagnosing an actual slow query or replication issue so they build intuition for how MySQL actually behaves under real load. Gradually giving them ownership over schema design decisions and query review for their team's features builds that judgment faster than any amount of documentation alone.

I'd ground the discussion in concrete tradeoffs, whether the new feature's access patterns and scale genuinely differ enough to justify operational overhead of another database, versus the simplicity and existing tooling benefits of staying in the shared instance. I'd push for a decision based on those specific technical tradeoffs rather than team preference or convenience, since the wrong call either way creates real cost down the line.

I'd first determine whether the problem is a genuine database limitation or a symptom of the application querying inefficiently, like fetching far more data than it needs or hitting the database far more often than necessary. Database-level fixes, indexing, configuration tuning, are usually cheaper and faster, so I'd exhaust those before recommending a more expensive and disruptive application-level redesign.

I'd anchor the roadmap in where the business itself is heading, projected data growth, new product lines with different data needs, rather than adopting new database technology or patterns just because they're trending. Building in deliberate checkpoints to reassess as actual growth and requirements become clearer keeps the roadmap grounded rather than locked into assumptions made too far in advance.

I'd start with an inventory of every instance and who owns it, since an unknown or forgotten instance is often the biggest actual risk. From there, checking each against a defined baseline, encryption in transit and at rest, least-privilege access, patch currency, gives a concrete, prioritized list of gaps rather than a vague sense that things could be better.

I'd weigh the operational and security benefits of standardization, easier patching, consistent tooling, faster incident response, against the cost of forcing every team through the same upgrade cycle regardless of their specific needs. In most organizations I'd lean toward a standard baseline with a documented, narrow exception process, rather than either rigid uniformity or unconstrained fragmentation.

I'd track concrete outcomes, reduced incident frequency and severity, improved query latency for user-facing features, reduced infrastructure cost per unit of load handled, rather than purely technical metrics that don't obviously connect to business impact. Regularly revisiting whether those tracked metrics still reflect what actually matters keeps the investment case honest as priorities shift over time.

I'd invest in reverse-engineering and documenting the current schema and its actual usage patterns before attempting any significant change, since undocumented legacy systems fail in surprising ways when touched carelessly. I'd push for incremental, well-tested changes with a clear rollback plan rather than a single large rewrite, given how much institutional risk is often hidden in a system nobody fully understands anymore.

I'd right-size infrastructure to genuine, measured need rather than provisioning generously out of caution, while making sure cost-cutting decisions don't quietly erode the reliability margins the business actually depends on. Framing infrastructure spend in terms of the business risk or user experience it protects, rather than treating it as pure overhead to minimize, keeps that conversation grounded in the right tradeoffs.

I'd periodically revisit whether MySQL genuinely remains the best fit for the organization's dominant workloads, and whether newer needs, like specialized analytics or unstructured data, are being forced awkwardly into MySQL when a purpose-built technology would serve them better. A mature data strategy uses the right tool for each genuine need rather than defaulting to a single technology everywhere out of institutional inertia.

I focus on getting them comfortable making the business case for infrastructure investments in terms leadership actually cares about, and having them own relationships with other engineering teams rather than only being consulted reactively when something breaks. Pairing them on organization-wide initiatives where they need to negotiate priorities and tradeoffs across teams builds the influence that deep technical skill alone doesn't teach.

I'd make the actual risk and cost of the technical debt concrete and specific, incident history, growing operational burden, features that are becoming harder to build safely on the current foundation, rather than arguing for debt reduction in the abstract. Even when the final prioritization decision goes against my recommendation, making sure it's an informed decision with the real tradeoffs understood matters more than winning the specific argument.

Beyond just operational reliability, a mature database engineering function should be proactively identifying where infrastructure limitations are quietly constraining what the business can build, and surfacing that before it becomes an urgent blocker on a critical initiative. I try to make sure database engineering is positioned as an enabler of what the business wants to do next, not only a team that keeps the lights on for what already exists.

Signals of needing fundamental change include chronic firefighting that never seems to reduce despite ongoing effort, a pattern of major incidents traced back to the same root causes repeatedly, or a structure that no longer matches how the business and its data needs have evolved. I'd rather diagnose root causes honestly and propose real structural change when it's warranted than keep patching symptoms with incremental fixes that don't address the underlying gap.

I push for that knowledge to live in documented runbooks, architecture decision records, and shared tooling rather than only in people's heads, and I deliberately involve less senior engineers in incident response and architecture discussions earlier than might feel comfortable so the reasoning spreads naturally through the team. Relying on a couple of people as the sole source of critical operational knowledge is a genuine organizational risk if either of them leaves.

I'd anchor the vision in where the business itself is heading over the next several years, then work backward to the infrastructure, tooling, and team capabilities that will need to be in place well before they become urgent. A vision built purely around adopting newer technology for its own sake, disconnected from where the business is actually going, tends to lose leadership buy-in quickly.

I'd weigh how core and differentiating the specific capability is to the business against the operational cost and risk of building and maintaining deep in-house expertise for it. A capability that's central to the company's competitive advantage generally justifies in-house investment despite higher upfront cost, while a more commoditized, well-solved problem is often better served by a managed service the organization doesn't need to operate itself.

I'd focus entirely on business outcomes and risk, reliability's connection to revenue and customer trust, the cost of underinvestment measured in outage impact, and deliberately leave out implementation detail that isn't relevant at that level. Board-level credibility comes from clear, confident framing of risk and impact, not from demonstrating technical depth that audience isn't positioned to evaluate.

I'd separate the immediate response, containing the damage and communicating transparently with affected stakeholders, from the longer root-cause investigation, resisting pressure to assign blame before the actual cause is fully understood. Turning the postmortem into concrete, tracked process and architecture changes matters more long-term than the specifics of any single incident.

I push for shared visibility into database health and performance metrics rather than keeping that information siloed within the database team, and I involve product engineering teams directly in decisions that affect their own data's schema and access patterns. Recognizing and reinforcing that reliability is a shared responsibility, beyond something one team is solely accountable for, changes how teams design and build against the database in the first place.

I'd weigh how urgently a specific capability or fix is needed against how long building genuine internal expertise for it would realistically take, and how core that capability is to the business's long-term competitive position. An urgent, complex problem the team doesn't yet have deep expertise in usually favors bringing in outside help now, while something central and ongoing is worth the longer internal investment, even if it means moving more slowly at first.

I'd assess whether decision-making and technical ownership are currently too concentrated in one or two individuals to scale, whether the organizational structure still matches how the business has grown and diversified, and whether the team has a genuine pipeline for developing the next generation of technical leaders. Proactively evolving the structure ahead of clear strain tends to go far better than waiting until the current structure has visibly broken under growth.