Author Archives: Stanislav_Panteleev

A primary key is a column (or a set of columns) in a database table that uniquely identifies each row in that table. Key points about a primary key: Uniqueness: No two rows can have the same value in the … Continue reading →

EXPLAIN in SQL The EXPLAIN (or sometimes EXPLAIN PLAN) command is used to display the execution plan of a SQL query. In simple terms: It shows how the database engine will execute your query. It helps understand and optimize query … Continue reading →

Excellent question! When multiple transactions access the same data concurrently, various data anomalies can occur — especially if isolation levels are low. These problems can cause inconsistent, incorrect, or even lost data if not handled properly. 🔍 Common Problems with … Continue reading →

Transaction isolation levels define how isolated a transaction is from others — i.e., how much one transaction can “see” the uncommitted changes of another. They are standardized by SQL ANSI and help balance data consistency vs performance. 🔒 The 4 … Continue reading →

The main properties of a transaction are summarized by the acronym ACID, which ensures reliability and consistency in database operations. 🔐 ACID Properties: Property Description A – Atomicity A transaction is all-or-nothing: either every operation succeeds, or none do. If … Continue reading →

💼 What is a Transaction in a Database? A transaction is a sequence of one or more SQL operations (such as INSERT, UPDATE, DELETE) that are executed as a single logical unit of work. A transaction must either complete fully … Continue reading →

Selectivity = the percentage of rows that match a given condition. High selectivity: few rows match→ e.g. WHERE email = ‘alice@example.com’→ ✅ index is efficient Low selectivity: many rows match→ e.g. WHERE gender = ‘M’ in a table where 60% … Continue reading →

Despite indexes often improving performance, there are important cases where a full table scan is actually better than using an index. ✅ When Is a Full Table Scan More Efficient? 1. When a Large Portion of the Table Matches If … Continue reading →

Great question — the answer is: Usually, no, it does not make sense to index columns with a small number of distinct values (i.e., low cardinality), unless there’s a very specific reason. 📉 Why Not? Columns like: Have very few … Continue reading →

✅ 1. In MySQL (InnoDB) 🔹 By default: The primary key becomes the clustered index. If no primary key is defined, InnoDB picks: the first unique NOT NULL index, or creates a hidden row_id. 🔹 To define your own clustered … Continue reading →