>Digital-flame

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 primary key column(s).
• Non-null: A primary key column cannot have NULL values; every row must have a valid, unique key.
• Single vs Composite: It can be a single column (like id) or a combination of columns (called a composite key).
• Purpose: It ensures that each record can be uniquely identified, which is crucial for data integrity and for creating relationships between tables.

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 performance.
• It tells you about table scans, indexes used, join algorithms, estimated costs, and other internal operations.

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 Concurrent Transactions:

1. 🧼 Dirty Read

One transaction reads uncommitted changes made by another.

Example:

• T1 updates balance = 500 → 600 but hasn’t committed.
• T2 reads balance = 600.
• T1 rolls back → T2 used invalid data.

🔒 Prevented by: READ COMMITTED and higher.

2. 🔁 Non-Repeatable Read

A row is read twice, and the value changes between reads within the same transaction.

Example:

• T1 reads user email = alice@mail.com.
• T2 updates it to alice@new.com and commits.
• T1 reads again: value changed.

🔒 Prevented by: REPEATABLE READ and SERIALIZABLE.

3. 👻 Phantom Read

A query returns different sets of rows when run twice in the same transaction due to new rows being inserted.

Example:

• T1: SELECT * FROM Orders WHERE total > 100;
• T2: inserts a new matching order and commits.
• T1 runs the same query again — now sees a new row.

🔒 Prevented by: SERIALIZABLE.

4. ❌ Lost Update

Two transactions read the same row, modify it, and write back, overwriting each other’s changes.

Example:

• T1 reads balance = 500, adds 100 → sets to 600
• T2 also reads 500, adds 200 → sets to 700
• Final result = 700 ❌ (T1’s change is lost)

🔒 Prevented by: proper locking, SERIALIZABLE, or optimistic concurrency control.

🧠 Summary Table:

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 Standard Isolation Levels (from lowest to highest):

🧪 What Does Each Level Do?

1. READ UNCOMMITTED

• Transactions can see uncommitted changes of others.
• Prone to dirty reads (reading data that may be rolled back).
• Rarely used due to danger of corrupt logic.

2. READ COMMITTED (default in many DBs)

• Can only read committed data.
• Prevents dirty reads, but still allows:
  • ❌ Non-repeatable reads (same query returns different results)
  • ❌ Phantom reads (rows appear/disappear in repeated queries)

3. REPEATABLE READ

• Prevents dirty reads and non-repeatable reads.
• Ensures same query in same transaction always sees same rows.
• Still allows phantom reads (new rows may show up if using SELECT WHERE)

4. SERIALIZABLE

• Strictest level: transactions are executed as if they were serial (one at a time).
• Prevents:
  • Dirty reads
  • Non-repeatable reads
  • Phantom reads
• Ensures full isolation but with lower concurrency and higher locking overhead.

💡 Summary Diagram:

READ UNCOMMITTED < READ COMMITTED < REPEATABLE READ < SERIALIZABLE
       (More concurrency)                         (More isolation)

The main properties of a transaction are summarized by the acronym ACID, which ensures reliability and consistency in database operations.

🔐 ACID Properties:

🧠 Example: Bank Transfer

BEGIN;

UPDATE Accounts SET balance = balance - 100 WHERE id = 1;
UPDATE Accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

If the system crashes after the first update:

• Atomicity ensures both updates are rolled back
• Consistency ensures no money is lost or created
• Isolation prevents interference from other transfers
• Durability ensures that if COMMIT runs, the transfer is saved

💼 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 or have no effect at all — there is no in-between.

🔒 Transactions follow the ACID properties:

🛠️ Example:

BEGIN;

UPDATE Accounts SET balance = balance - 100 WHERE id = 1;
UPDATE Accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

✅ If both updates succeed: COMMIT makes them permanent

❌ If any fails: ROLLBACK undoes all changes

🔄 Commands:

🔍 Why Use Transactions?

• To transfer money safely between accounts
• To batch multiple operations that must succeed together
• To maintain data integrity in case of errors or crashes

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% are 'M'
  → ❌ index might be inefficient

🧠 Why Is Low Selectivity Bad for Index Use?

When many rows match, using an index means:

1. The database finds matching row IDs in the index
2. Then it has to jump to each row individually (called random I/O)
3. This is slower than just reading the whole table sequentially in memory or disk

✅ Example:

Assume this table has 1 million rows:

SELECT * FROM Users WHERE is_active = true;

If 950,000 rows have is_active = true, that’s very low selectivity

The index on is_active matches most of the table

The database says:
❌ “Too many matches — don’t waste time jumping around.”
✅ “Just scan the whole table — it’s faster.”

Visual Analogy:

• 📇 Index access is like flipping through a card catalog to find books, but running back and forth in the library for each one.
• 📚 Full table scan is like walking down every shelf and grabbing all relevant books in order — more efficient if you’re taking most of them.

🧪 Rule of Thumb:

• If a condition matches >10–20% of the table, many databases prefer a full scan over index access.

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 a query needs to scan a high percentage of rows (e.g., >10–20%), a sequential scan is often faster than jumping around via an index.

✅ Example:

SELECT * FROM Orders WHERE order_date > '2000-01-01';

if 90% of the table matches, the index offers little benefit — the DB just scans the whole table efficiently.

2. Low Selectivity (Few Distinct Values)

• If you filter on a low-cardinality column, the index doesn’t help much.

✅ Example:

SELECT * FROM Users WHERE gender = 'M';

If half of all rows are 'M', the DB will often choose a full scan.

3. Fetching All or Most Columns

• Indexes store only some columns.
• If you’re selecting all columns, the database may do an index lookup + row fetch for each row, which is slower than just reading the table directly.

✅ Example:

SELECT * FROM Products WHERE price > 10;

Even if there’s an index on price, if all columns are needed, the index might cause extra disk reads (called a “bookmark lookup”).

4. Small Tables

• For small tables, it’s often faster to scan the whole thing than to use an index.
• Index overhead may not be worth it for a 10-row table.

5. Outdated or Non-existent Statistics

• If the database query planner lacks good stats, it might default to a full scan.
• Also true if no suitable index exists.

6. Queries with Functions or Calculations on Indexed Columns

• Expressions disable index use unless functional indexes exist.

❌ Example:

SELECT * FROM Users WHERE UPPER(email) = 'ALICE@EXAMPLE.COM';

📊 Summary Table

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:

gender = 'M' or 'F'
status = 'active' or 'inactive'
is_deleted = true or false

Have very few possible values, so:

• Each index entry matches many rows
• The database will likely do a full table scan anyway, because the index isn’t selective
• Using the index can be slower than just scanning the table

🔍 When Might It Still Make Sense?

✅ 1. In combination with other columns (composite index)

CREATE INDEX idx_status_created ON Users(status, created_at);

Useful if you often query:

SELECT * FROM Users
WHERE status = 'active' AND created_at > '2024-01-01';

Here, status helps narrow the data before applying the range on created_at.

✅ 2. In large, filtered queries or partitions

If:

• The table is huge, and
• The low-cardinality column is highly skewed (e.g., 90% 'active', 10% 'inactive'), and
• You often query the rare value:

SELECT * FROM Users WHERE status = 'inactive';

Then an index might help — but it depends on the query planner and row distribution.

✅ 3. Bitmap Indexes (in data warehouses)

In OLAP databases (e.g., Oracle, Redshift), low-cardinality columns are perfect for bitmap indexes, which compress well and are efficient for complex filtering.

🧠 Rule of Thumb:

✅ 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 index:

You just declare a primary key, and it becomes clustered:

CREATE TABLE Users (
    user_id INT NOT NULL,
    name VARCHAR(100),
    email VARCHAR(100),
    PRIMARY KEY (user_id)  -- this is the clustered index
);

To use a different column:

CREATE TABLE Orders (
    order_number INT,
    customer_id INT,
    order_date DATE,
    PRIMARY KEY (order_date, order_number)  -- clustered index on (order_date, order_number)
);

🧠 MySQL does not let you create a clustered index directly by name — it’s always tied to the primary key in InnoDB.

✅ 2. In SQL Server

Here you have full control and can explicitly create a clustered index:

CREATE CLUSTERED INDEX idx_order_date
ON Orders(order_date);

You can also specify it during table creation:

CREATE TABLE Products (
    product_id INT,
    name VARCHAR(100),
    price DECIMAL(10, 2),
    CONSTRAINT pk_product PRIMARY KEY CLUSTERED (product_id)
);

✅ 3. In PostgreSQL

PostgreSQL does not support true clustered indexes — but it allows clustering once, and you must re-cluster manually when data changes.

CREATE INDEX idx_order_date ON Orders(order_date);
CLUSTER Orders USING idx_order_date;

⚠️ PostgreSQL won’t maintain the clustering automatically.

🧠 Summary Table

🔑 In simple terms:

A clustered index determines the default physical order of rows in the table — the table is stored on disk sorted by this index.

So yes:

• When you define a clustered index, the table rows are physically arranged in that order.
• It’s like saying: “This is the natural sort order of the table.”

📌 Key Points Recap: