Skip to main content

8. MySQL & Databases

How to connect to a MySQL database using PHP?​

Junior

Answer: You can use the mysqli extension or PDO (PHP Data Objects).

// PDO Example
$pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'pass');

What is the difference between mysqli and PDO?​

Junior

Answer:

  • mysqli is specific to MySQL, while PDO supports multiple database systems (PostgreSQL, SQLite, etc.).
  • PDO supports named placeholders in prepared statements.

What is the difference between WHERE and HAVING?​

Junior

Answer: WHERE filters rows before aggregation, while HAVING filters after aggregation (usually with GROUP BY).

What is a Primary Key?​

Junior

Answer: A primary key is a column (or a set of columns) that uniquely identifies each record in a table. It cannot be null.

What is the difference between CHAR and VARCHAR?​

Junior

Answer: CHAR is fixed-length (padded with spaces), while VARCHAR is variable-length (uses only the necessary space plus 1-2 bytes for length). CHAR is slightly faster for very short, uniform data (like country codes), while VARCHAR saves space for varying text lengths.

What are the main numeric data types in MySQL?​

Junior

Answer:

  • TINYINT: 1 byte (-128 to 127).
  • SMALLINT: 2 bytes (-32,768 to 32,767).
  • INT: 4 bytes (-2.1B to 2.1B).
  • BIGINT: 8 bytes.
  • DECIMAL: Fixed-point for financial data (exact precision).
  • FLOAT/DOUBLE: Floating-point for approximate values.

What is the difference between DATETIME and TIMESTAMP?​

Junior

Answer:

  • DATETIME: 8 bytes. Range: 1000-01-01 to 9999-13-31. Constant across time zones.
  • TIMESTAMP: 4 bytes. Range: 1970-01-01 to 2038-01-19. Converted to/from UTC based on the current time zone.

What are the ENUM and SET types?​

Junior

Answer:

  • ENUM: A string object that can have only one value chosen from a list of permitted values.
  • SET: A string object that can have zero or more values chosen from a list of permitted values (up to 64).

What is the difference between BLOB and TEXT?​

Junior

Answer: Both store large amounts of data, but TEXT is for character strings (respects character sets/collations), while BLOB (Binary Large Object) is for binary data (images, files) and is treated as a byte string.

What is the default port for MySQL?​

Junior

Answer: 3306.

What is the difference between CHAR_LENGTH and LENGTH?​

Junior

Answer: CHAR_LENGTH counts the number of characters, while LENGTH counts the number of bytes.

What do % and _ represent in a LIKE statement?​

Junior

Answer:

  • % matches any sequence of characters (zero or more).
  • _ matches exactly one character.

What are the different types of Joins in MySQL?​

Junior

Answer:

  • (INNER) JOIN: Returns records that have matching values in both tables.
  • LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table.
  • RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table.
  • CROSS JOIN: Returns the Cartesian product of the two tables (every row from the first table joined with every row from the second).
  • Self Join: A regular join, but the table is joined with itself.

What is the difference between an Implicit and Explicit Join?​

Junior

Answer:

  • Implicit Join: Uses the FROM clause to list multiple tables and the WHERE clause to specify the join condition (e.g., SELECT * FROM TableA, TableB WHERE TableA.id = TableB.a_id).
  • Explicit Join: Uses the JOIN keyword and the ON clause to specify the join condition (e.g., SELECT * FROM TableA JOIN TableB ON TableA.id = TableB.a_id). Explicit joins are generally preferred for readability and avoiding accidental Cartesian products.

What are the main string functions in MySQL?​

Junior

Answer:

  • CONCAT(str1, str2, ...): Joins two or more strings.
  • CONCAT_WS(separator, str1, str2, ...): Joins strings with a separator.
  • LENGTH(str) / CHAR_LENGTH(str): Returns the length of a string in bytes/characters.
  • LEFT(str, len) / RIGHT(str, len): Returns a specified number of characters from the start/end.
  • SUBSTRING(str, start, len): Extracts a substring.
  • SUBSTRING_INDEX(str, delimiter, count): Returns a substring before a specified number of delimiter occurrences.
  • UPPER(str) / LOWER(str): Converts to uppercase/lowercase.
  • TRIM(str) / LTRIM(str) / RTRIM(str): Removes leading/trailing spaces.
  • REPLACE(str, from_str, to_str): Replaces occurrences of a substring.
  • INSERT(str, start, len, new_str): Replaces a portion of a string with another string.
  • LOCATE(substr, str): Returns the position of the first occurrence of a substring.
  • REVERSE(str): Reverses the characters in a string.
  • REPEAT(str, count): Repeats a string a specified number of times.
  • LPAD(str, len, pad) / RPAD(str, len, pad): Pads a string to a certain length.

What are the main numeric functions in MySQL?​

Junior

Answer:

  • ABS(n): Returns the absolute value.
  • CEIL(n) / CEILING(n): Rounds up to the nearest integer.
  • FLOOR(n): Rounds down to the nearest integer.
  • ROUND(n, d): Rounds to a specified number of decimal places.
  • TRUNCATE(n, d): Truncates to a specified number of decimal places.
  • RAND(): Returns a random floating-point value between 0 and 1.
  • SQRT(n): Returns the square root.
  • POWER(x, y): Returns $x$ raised to the power of $y$.
  • SIGN(n): Returns -1, 0, or 1 depending on whether the number is negative, zero, or positive.

What are the main date and time functions in MySQL?​

Junior

Answer:

  • NOW() / SYSDATE() / CURRENT_TIMESTAMP(): Returns current date and time.
  • CURDATE() / CURRENT_DATE(): Returns current date.
  • CURTIME() / CURRENT_TIME(): Returns current time.
  • UTC_DATE() / UTC_TIME(): Returns current UTC date/time.
  • DATE_ADD(date, INTERVAL expr unit) / DATE_SUB(...): Adds/subtracts time intervals.
  • DATEDIFF(expr1, expr2): Returns the number of days between two dates.
  • DATE_FORMAT(date, format): Formats a date as specified.
  • EXTRACT(unit FROM date): Extracts a part of the date (YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, etc.).
  • DAYOFMONTH(), DAYOFWEEK(), DAYOFYEAR(), LAST_DAY(): Returns specific day information.
  • MONTH(), YEAR(), QUARTER(), WEEK(): Returns specific date parts.
  • HOUR(), MINUTE(), SECOND(): Returns specific time parts.
  • TIME_TO_SEC(time): Converts time to seconds since midnight.

What are the flow control functions in MySQL?​

Junior

Answer:

  • IF(expr1, expr2, expr3): If expr1 is true, returns expr2, else expr3.
  • IFNULL(expr1, expr2): If expr1 is not NULL, returns expr1, else expr2.
  • COALESCE(list): Returns the first non-NULL value in the list.
  • CASE WHEN condition THEN result [ELSE result] END: Evaluates a list of conditions and returns the corresponding result.

What is the difference between WHERE and HAVING?​

Junior

Answer: WHERE filters rows before aggregation, while HAVING filters after aggregation (usually with GROUP BY).

What is the difference between a WHERE clause and a JOIN ... ON condition?​

Junior

Answer:

  • JOIN ... ON: Defines how tables are linked together. It is processed during the join operation.
  • WHERE: Filters the resulting rows after the join has been logically formed.
  • Performance: For INNER JOIN, they are often functionally equivalent as the optimizer can move conditions between them. For OUTER JOIN (LEFT/RIGHT), the placement is critical: a condition in ON determines which rows are joined (keeping all rows from the "outer" table), while a condition in WHERE filters the final result set (potentially turning a LEFT JOIN into an INNER JOIN if it filters out NULLs).

What is the EXPLAIN statement and how to read its output?​

Middle

Answer: The EXPLAIN statement provides information about how MySQL executes a query. It shows which indexes are used, how tables are joined, and the estimated number of rows examined. Key columns include type (join type, where const and ref are good, while ALL is a full table scan), key (the actual index used), and Extra (additional info like Using filesort or Using temporary, which often indicate performance issues).

Detailed Guide on EXPLAIN

What is a Transaction?​

Middle

Answer: A sequence of database operations that are treated as a single unit of work. They must follow the ACID properties.

What are the main storage engines in MySQL?​

Middle

Answer:

  • InnoDB: Supports transactions, row-level locking, and foreign keys (Default).
  • MyISAM: Does not support transactions, uses table-level locking, faster for read-heavy operations.
  • MEMORY: Stores data in RAM for very fast access, but data is lost if the server restarts.

What is a View?​

Middle

Answer: A virtual table based on the result-set of an SQL query. It does not store data itself.

What is a Trigger?​

Middle

Answer: A database object that automatically executes in response to certain events (INSERT, UPDATE, DELETE) on a particular table.

What is InnoDB?​

Middle

Answer: A storage engine for MySQL that supports transactions, foreign keys, and row-level locking. It is the default engine.

What is an Index and why is it used?​

Middle

Answer: An index is a data structure used to quickly locate records in a table. It improves query speed but can slow down inserts/updates.

How are indexes stored?​

Middle

Answer: Most database indexes (including MySQL's InnoDB) are stored as B-trees (specifically B+Trees). They are "horizontally large" (high fan-out) and shallow, which minimizes the number of disk I/O operations needed to find a value. This structure allows for fast filtering (O(log n)) and efficient range scans. Proper ordering in composite indexes is crucial: generally, equality conditions and high-selectivity columns should come first, followed by range conditions.

What is the difference between UNION and UNION ALL?​

Middle

Answer: UNION removes duplicate rows from the result set, while UNION ALL includes all rows, including duplicates. To use UNION, the number and data types of columns in both SELECT statements must match.

How to perform conditional calculations in a SELECT query?​

Middle

Answer: Use the CASE statement or IF() function. For example, to apply different interest rates based on a balance:

SELECT name, balance,
  CASE
    WHEN balance < 3000 THEN balance * 1.1
    ELSE balance * 1.3
  END AS total
FROM accounts;

What is the difference between IN and BETWEEN?​

Middle

Answer:

  • IN: Checks if a value matches any value in a specified list or subquery.
  • BETWEEN: Checks if a value falls within a specified range (inclusive of start and end values).

What are Aggregate Functions in MySQL?​

Middle

Answer: Functions that perform a calculation on a set of values and return a single value. Common ones: COUNT(), SUM(), AVG(), MIN(), MAX().

What is the purpose of GROUP BY and HAVING?​

Middle

Answer: GROUP BY groups rows that have the same values into summary rows. HAVING is used to filter these groups based on a condition (since WHERE cannot be used with aggregate functions).

What is a Subquery?​

Middle

Answer: A query nested inside another query (SELECT, INSERT, UPDATE, or DELETE). They can be correlated (dependent on the outer query) or non-correlated (independent).

What is the EXISTS operator?​

Middle

Answer: A logical operator used in a WHERE clause to check if a subquery returns any rows. It returns true if the subquery returns one or more records. It is often more efficient than IN for checking existence.

What is the difference between ANY and ALL when used with subqueries?​

Middle

Answer:

  • ANY (or SOME): Returns true if the comparison is true for at least one value in the subquery result.
  • ALL: Returns true only if the comparison is true for all values in the subquery result.

What are the common column constraints (attributes)?​

Middle

Answer:

  • NOT NULL: Ensures a column cannot have a NULL value.
  • UNIQUE: Ensures all values in a column are different.
  • PRIMARY KEY: A combination of NOT NULL and UNIQUE.
  • FOREIGN KEY: Prevents actions that would destroy links between tables.
  • DEFAULT: Sets a default value for a column if none is specified.
  • CHECK: Ensures that the values in a column satisfy a specific condition.
  • AUTO_INCREMENT: Automatically generates a unique number for each new row.

What are the ON DELETE and ON UPDATE actions for Foreign Keys?​

Middle

Answer:

  • CASCADE: Automatically delete or update rows in the child table when the parent row is deleted/updated.
  • SET NULL: Set the foreign key column in the child table to NULL.
  • RESTRICT / NO ACTION: Reject the delete or update operation in the parent table if related rows exist in the child table.

What is the CONSTRAINT operator used for?​

Middle

Answer: It is used to define and name constraints (PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK) on a table. Naming constraints allows you to easily drop or modify them later using their names.

What is the difference between LIKE and REGEXP?​

Middle

Answer:

  • LIKE: Uses simple wildcards (% for any string, _ for any single character).
  • REGEXP: Uses powerful regular expressions (^, $, ., [ ], |, etc.) for complex pattern matching.

How can you modify an existing table structure?​

Middle

Answer: Using the ALTER TABLE command to add, delete, or modify columns and constraints.

What is the difference between MariaDB and MySQL?​

Middle

Answer: MariaDB is an open-source fork of MySQL created by the original developers after Oracle's acquisition of MySQL. While it maintains high compatibility, MariaDB offers more storage engines, performance improvements, and remains fully open-source (GPL), whereas MySQL has both a community and an enterprise version. MariaDB vs MySQL Comparison

What is Sharding and Partitioning?​

Senior

Answer:

  • Partitioning: Splitting a large table into smaller, more manageable pieces within the same database server.
  • Sharding: Distributing data across multiple independent database servers (horizontal scaling).

What are ACID properties?​

Senior

Answer:

  • Atomicity: All-or-nothing (everything succeeds or fails).
  • Consistency: Transactions must transition the database from one valid state to another.
  • Isolation: Transactions occur independently of each other.
  • Durability: Committed data is permanently saved, even in a system crash.

What is a Clustered Index vs a Non-Clustered Index?​

Senior

Answer:

  • Clustered Index: Determines the physical order of data in the table. There can only be one per table (usually the primary key).
  • Non-Clustered Index: A separate structure that contains the indexed values and pointers to the data rows.

What isolation levels do you know?​

Senior

Answer: The standard isolation levels are Read Uncommitted, Read Committed, Repeatable Read, and Serializable. They define the trade-off between consistency and performance in concurrent transactions. In-depth Database Isolation Levels Guide

What is the difference between Read Committed and Dirty Read?​

Senior

Answer: A Dirty Read occurs in Read Uncommitted where a transaction sees uncommitted data from another transaction. Read Committed prevents this by ensuring a transaction only sees data that has already been committed. In-depth Database Isolation Levels Guide

Provide examples of transaction isolation levels and the issues they address.​

Senior

Answer:

  • Read Uncommitted: Reading a bank balance being updated but not yet committed (Dirty Read).
  • Read Committed: A report reading the same data twice and getting different results because another transaction committed an update in between (Non-Repeatable Read).
  • Repeatable Read: Ensuring a consistent snapshot of data throughout a long-running transaction, even if other transactions commit changes.
  • Serializable: Financial systems where absolute correctness is required, preventing all concurrency issues like Phantom Reads at the cost of performance. In-depth Database Isolation Levels Guide

What is the difference between MySQL and PostgreSQL?​

Senior

Answer: MySQL is known for its speed and ease of use in web development, while PostgreSQL is an object-relational database known for its advanced features, strict SQL compliance, and better handling of complex queries and high-concurrency workloads. MySQL vs PostgreSQL Comparison

We have a filter with 3 columns from one table: sku (unique), creation_date, and an integer_field (values 1, 2, or 3). How would you index this for maximum performance?​

Senior

Answer: Create a composite index including all three fields. For maximum performance, the order should be: sku (highest selectivity), then integer_field (exact matches), and finally creation_date (range filters). This order ensures that the most selective filter narrows down the results immediately, while the range filter comes last to allow the preceding parts of the index to be fully utilized. Composite Index Strategy

You have this SQL query. Is something wrong about it and how would you improve it?​

SELECT * FROM table ORDER BY id LIMIT :limit OFFSET :offset

  • The id is Primary Key, AutoIncrement, and indexed.
Senior

Answer:

Using OFFSET for pagination is inefficient for large datasets because MySQL must scan and discard all rows before the offset. For example, OFFSET 1000000 requires reading 1 million rows before returning the requested ones, leading to high I/O and CPU usage.

Optimization (Cursor-based Pagination):

  • Initial Request: SELECT * FROM table ORDER BY id LIMIT :limit
  • Subsequent Requests: Get the last ID from the current page (e.g., :last_id) and use it to skip previous rows: SELECT * FROM table WHERE id > :last_id ORDER BY id LIMIT :limit

This ensures the query remains fast regardless of the page number, as it uses the primary key index to jump directly to the starting point.

You have these SQL queries, what indexes would you use?​

  1. SELECT * FROM transactions WHERE account_id = :account_id AND transaction_type = :transaction_type AND created_at BETWEEN :start AND :end
  2. SELECT * FROM transactions WHERE account_id = :account_id AND transaction_type = :transaction_type ORDER BY created_at
  • account_id: bigint, ~100.000 variants
  • transaction_type: int, 15 variants
  • created_at: DATETIME, DEFAULT NOW()
Senior

Answer:

Use a composite index: idx (account_id, transaction_type).

Reasoning:

  • It is better to filter first by account_id to minimize the scope significantly (high selectivity).
  • Then, filter by transaction_type.
  • In the second query, created_at is used for ordering. While adding it to the index could avoid a filesort, it might not always be beneficial to include it if it's not used to narrow down the data much in most queries, or if the filtered result set is small enough that a post-select order is efficient.

You have this code, is there anything wrong about it?​

public function addBalance(float $sum, User $user) {
    $balance = $this->repo->getUserBalance($user->getId());
    $balance += $sum;
    $this->repo->updateUserBalance($balance, $user->getId());
}
// In getUserBalance: SELECT balance FROM Users WHERE id = :id
// In updateUserBalance: UPDATE Users SET balance = :balance WHERE id = :id
Senior

Answer:

This code is vulnerable to race conditions (Lost Updates). If two concurrent processes read the same balance before either updates it, one update will overwrite the other.

In-depth explanation:

  • At the weakest isolation level (Read Uncommitted), a Dirty Read might occur (reading uncommitted data).
  • Even with default isolation levels, concurrent "read-modify-write" cycles lead to data loss.
  • Solution: Use SELECT ... FOR UPDATE in MySQL. This locks the specific row, forcing other transactions to wait until the lock is released (after the UPDATE is committed).

Correct Implementation:

$this->db->transaction(function() use ($sum, $user) {
    // SELECT balance FROM Users WHERE id = :id FOR UPDATE
    $balance = $this->repo->getUserBalanceForUpdate($user->getId());
    $balance += $sum;
    $this->repo->updateUserBalance($balance, $user->getId());
});

Can you provide concrete SQL examples of how different isolation levels and locks behave in a concurrent environment?​

Senior

Answer:

Understanding the exact behavior of isolation levels and locks is crucial for debugging race conditions. Detailed step-by-step scenarios (including T1/T2 timelines) for Dirty Reads, Non-Repeatable Reads, and Phantoms, as well as concrete examples of Exclusive (FOR UPDATE) vs. Shared (LOCK IN SHARE MODE) locks and Deadlocks, are provided in the guide. Detailed MySQL Isolation and Locking Scenarios

How to ensure idempotency when inserting records (e.g., in a message broker consumer)?​

Senior

Answer: Use Unique Constraints on relevant business keys (e.g., (order_id, user_id)). When a message is retried (at-least-once delivery), the database will block the second insert. Your application should catch the "Duplicate Key" error and treat it as a success or a known state to ensure consistent results.

How to manage capacity limits (e.g., maximum attendees) in a high-concurrency environment?​

Senior

Answer:

  1. Pessimistic Locking: SELECT * FROM meetups WHERE id = 1 FOR UPDATE; to serialize all registrations for that meetup.
  2. Denormalization + CHECK Constraint: Add a booked_seats column and CHECK (booked_seats <= total_seats). Update the counter in the same transaction as the registration. This lets the DB enforce the limit. Detailed Guide on SQL Concurrency and Limits

What are xmin and xmax in the context of database rows (e.g., PostgreSQL)?​

Senior

Answer: These are hidden system columns used for MVCC (Multi-Version Concurrency Control).

  • PostgreSQL:
    • xmin: Stores the ID of the transaction that inserted the row.
    • xmax: Stores the ID of the transaction that deleted or updated the row.
  • MySQL (InnoDB):
    • DB_TRX_ID: Similar to xmin, stores the ID of the transaction that created the row version.
    • DB_ROLL_PTR: Pointer to the previous version in the Undo Log (used to reconstruct older versions).
    • DB_ROW_ID: Hidden row ID if no primary key exists. Understanding these is key to explaining how different transactions "see" different versions of data simultaneously without blocking each other.

When should you use Redis for locking instead of native SQL features?​

Senior

Answer: Use Redis for Rate Limiting, Circuit Breakers, or Cross-Node Task Synchronization (e.g., ensuring a cron job runs once). Avoid using Redis for data consistency inside the database. SQL features (Unique Indexes, Constraints, Row Locks) are more robust, handle failure scenarios better (no TTL issues), and don't add extra points of failure or overhead to the data layer.