02. MySQL Data Types Deep Dive: VARCHAR vs TEXT, DECIMAL for Financial Precision & JSON Column Performance • MySQL Mastery: Basic to Advanced & Enterprise Scaling • Rishabh Ki Kaksha

MySQL Mastery: Basic to Advanced & Enterprise Scaling › 02. MySQL Data Types Deep Dive: VARCHAR vs TEXT, DECIMAL for Financial Precision & JSON Column Performance

40 mins

← Prev Next →

Course Syllabus

Module 1: Relational Algebra, SQL Fundamentals & Schema Design

01. Relational Database Foundations: SQL vs NoSQL, Relational Algebra & Database Normalization (1NF to BCNF) 02. MySQL Data Types Deep Dive: VARCHAR vs TEXT, DECIMAL for Financial Precision & JSON Column Performance 03. Primary Keys, Foreign Keys, Unique Constraints & Referential Integrity Rules (CASCADE / RESTRICT)

Module 2: Advanced SQL: Joins, Window Functions & CTEs

04. SQL Joins Masterclass: INNER, LEFT, RIGHT, FULL OUTER & Cross Joins Execution Strategies 05. Subqueries vs Common Table Expressions (WITH CTE) & Recursive CTEs for Hierarchical Tree Queries 06. SQL Window Functions: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), LEAD() & LAG() for Analytics

Module 3: InnoDB Storage Engine Internals, Buffer Pool & B+ Trees

07. InnoDB Architecture: The Buffer Pool, Change Buffer, Redo Log (WAL), Undo Log & Doublewrite Buffer 08. Clustered Indexes vs Secondary Indexes: How B+ Tree Data Pages are Organized on Disk 09. The B+ Tree Node Structure: Fanout, Height Calculations & Why B+ Trees Outperform Binary Trees for Disk I/O

Module 4: ACID Transactions, Lock Architecture & Concurrency Control

10. ACID Properties Deep Dive: Atomicity (Undo Log), Consistency, Isolation & Durability (Redo Log) 11. Transaction Isolation Levels: Read Uncommitted, Read Committed, Repeatable Read (Default) & Serializable 12. Multi-Version Concurrency Control (MVCC): Read Views, Row Versioning & Non-Locking Consistent Reads 13. InnoDB Locking Engine: Record Locks, Gap Locks, Next-Key Locks & Resolving Deadlocks in High Concurrency

Module 5: Query Optimization, Index Tuning, Partitioning & High Availability

14. Query Optimization with EXPLAIN & EXPLAIN ANALYZE: Type column (ALL, index, range, ref, eq_ref, const) 15. Index Tuning Secrets: Leftmost Prefix Rule, Covering Indexes (Using Index) & Avoiding Index Degradation Traps 16. Table Partitioning: Range, List, Hash & Key Partitioning for Multi-Million Row Tables 17. MySQL Replication: Binary Log (binlog), Master-Slave Async vs Semi-Sync Replication & GTID Architecture 18. Production Enterprise Backup Strategies: Physical (Percona XtraBackup) vs Logical (mysqldump / mydumper)

Interactive Lesson Studio

# 02. MySQL Data Types Deep Dive: VARCHAR vs TEXT, DECIMAL for Financial Precision & JSON Column Performance

> MySQL Mastery Notes by Rishabh - Topic 02

---

### 1. Data Type Storage Internals 📦

*   **`VARCHAR(N)`:** Variable-length string up to 65,535 bytes. Stores 1-2 length prefix bytes in-row on 16KB data page.
*   **`TEXT` / `BLOB`:** Stored **Off-Page (Overflow Pages)**. In-row sirf 20-byte pointer rehta hai (Queries scanning TEXT create temporary disk tables!).
*   **`DECIMAL(P, S)` (e.g. `DECIMAL(10, 2)`):** Exact fixed-point binary representation (Zero floating-point rounding errors! Mandatory for financial currency calculations).
*   **`FLOAT` / `DOUBLE`:** IEEE 754 floating point (Fast for scientific calculations, but inexact: $0.1 + 0.2 \ne 0.3$).

---

### 2. High-Performance Native JSON Columns (MySQL 8.0)

```sql
CREATE TABLE user_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    metadata JSON NOT NULL,
    -- Generated Virtual Column for B+ Tree Indexing on JSON key:
    theme VARCHAR(20) AS (metadata->>'$.theme') STORED,
    INDEX idx_theme (theme)
);

-- JSON Query with Path Operator:
SELECT name, metadata->>'$.theme' AS theme
FROM user_profiles
WHERE JSON_EXTRACT(metadata, '$.is_pro') = true;
```

---

### 3. Interview Ke 3 Sawal Is Topic Se

1. **`DATETIME` vs `TIMESTAMP` me kya difference hai?** -> `DATETIME` fixed 8-byte date store karta hai (Timezone agnostic: '1000-01-01' to '9999-12-31'); `TIMESTAMP` 4-byte UTC timestamp store karta hai aur client timezone me auto-convert hota hai (Max limit: Year 2038 problem!).
2. **`CHAR(N)` vs `VARCHAR(N)` kab choose karein?** -> Fixed-length strings (UUIDs, MD5 hashes, Country ISO codes like `'IN'`, `'US'`) ke liye `CHAR` faster hota hai with zero memory fragmentation.
3. **JSON columns par direct B+ Tree index kyu nahi banta?** -> Kyunki JSON variable nested structure hota hai; MySQL me Generated Virtual Column bana kar us column par B+ Tree index banaya jata hai.

---
**Next Topic:** 03. Primary Keys, Foreign Keys, Unique Constraints & Referential Integrity Rules (CASCADE / RESTRICT)

Loading lesson content...

🎯 Big Tech Coding & Interview Challenges

Practice these curated problems. Click "Load in Sandbox" to solve live in the Code Studio!

3 Questions

Easy

1. Fundamental Warmup: 02. Filtering, Sorting, Aggregations & Group By

Ek clean function likhein jo basic boundary conditions aur edge cases (empty input, null pointers) ko validate karke exact expected value return kare.

                                        Input: [2, 7, 11, 15], Target = 9

Output: [0, 1]

Explanation: Index 0 aur 1 ka sum 2 + 7 = 9 hai.                                    

💡 View Pro Interview Hint

• Pehle brute-force approach sochein, phir dictionary / hashmap se lookup O(1) me reduce karein.

• Corner cases: Jab input array empty ho ya single element ho.

Medium

2. Big Tech Intermediate: Scalability & Memory Bounds

Is pattern ko large production dataset (100,000+ items) ke sath run karein bina unnecessary memory allocations ya Garbage Collection delays ke.

                                        Input: Stream of continuous data chunks

Output: Aggregated result within O(N) time and O(1) extra auxiliary memory.                                    

💡 View Pro Interview Hint

• Two-pointer approach ya in-place modifications consider karein.

• Unnecessary intermediate object allocations se bachein.

Hard

3. Big Tech L6 Interview Round: Distributed / Concurrent Edge

Scenario: Multi-threaded / Concurrent environment me jab race conditions aur deadlock possibilities hon, toh thread-safe solution design karein.

                                        Input: Concurrent requests calling the same critical section

Output: Exactly-once execution with zero race conditions.                                    

💡 View Pro Interview Hint

• Atomic operations, Mutex locks ya Lock-free data structures ka use karein.

• Timeouts aur retry backoff mechanisms enforce karein.

MySQL

Full IDE

Live Execution Terminal

Click "Execute Live" to run code.
                

🎯 Big Tech Coding & Interview Challenges

1. Fundamental Warmup: 02. Filtering, Sorting, Aggregations & Group By

2. Big Tech Intermediate: Scalability & Memory Bounds

3. Big Tech L6 Interview Round: Distributed / Concurrent Edge

Lesson Mastery AI Quiz

AI Doubt Solver

CERTIFICATE OF COMPLETION

Rishabh Gehlot

PHP 8+ & Laravel 11 Master Track