Questions
10 of 15
1What are the main categories of data types available in MySQL?
2What is the difference between CHAR and VARCHAR data types?
3Which data type would you use to store dates and times in MySQL?
4What is the difference between INT, FLOAT, and DECIMAL data types?
5What is the use of the TEXT and BLOB data types, and how are they different from VARCHAR?
6How does MySQL handle precision and scale in DECIMAL(M, D) columns internally, and how do these differ from FLOAT and DOUBLE in terms of storage and accuracy?
7When storing time zone–aware data, what are the differences in behavior and use cases between DATETIME, TIMESTAMP, and CONVERT_TZ() in MySQL?
8If you define a VARCHAR(255) column with utf8mb4 encoding, how does MySQL calculate the maximum possible storage size for that column, and how does it differ from CHAR(255)?
9What are the advantages and limitations of using ENUM and SET data types in terms of performance, flexibility, and schema evolution?
10Explain how MySQL internally stores and sorts values of type BLOB and TEXT. What happens when you try to index a TEXT column?
11How do signed and unsigned integer types affect query results, index usage, and storage size? Can you demonstrate an example where overflow behavior differs?
12In what scenarios would using a JSON column be preferable to a normalized table structure, and what are the internal storage and indexing implications of JSON in MySQL 8.0?
13How does MySQL’s BIT(M) type differ from BOOLEAN, TINYINT(1), and binary string types (BINARY, VARBINARY) in terms of storage, representation, and retrieval?
14If you define a composite index on multiple columns of different data types (e.g., INT, VARCHAR, and DATE), how do the internal data type differences influence sorting, comparisons, and index efficiency?
15What are the practical implications of using CHAR vs VARCHAR for columns in InnoDB tables with varying row lengths and frequent updates? How does this choice affect row fragmentation and performance?
mysqlmysql
Basics
Databases
Tables
Data Types
Queries
Views
Indexes
Clauses
Regex
Functions and Operators
Keys
Joins
Triggers
10 / 15

Explain how MySQL internally stores and sorts values of type BLOB and TEXT. What happens when you try to index a TEXT column?

Internal Storage, Sorting, and Indexing Behavior of BLOB and TEXT

MySQL stores BLOB and TEXT values differently from normal string or numeric columns. Their size and flexible length affect how MySQL sorts and indexes them.

How MySQL Stores BLOB and TEXT Internally
  1. 1

    Both BLOB and TEXT are stored off-page when values are large, with only a pointer kept in the table row.

  2. 2

    Small values may be stored inline depending on the storage engine (InnoDB stores first 768 bytes inline).

  3. 3

    BLOB stores binary data with no character set or collation.

  4. 4

    TEXT stores character data and supports collation-based sorting.

How Sorting Works for BLOB and TEXT
  1. 1

    TEXT sorting uses character set and collation rules, which may be slower due to case and accent handling.

  2. 2

    BLOB sorting is byte-by-byte (binary sorting), without collation or case-insensitivity.

  3. 3

    Sorting large TEXT/BLOB values is slower because MySQL may need to fetch off-page data.

What Happens When You Index a TEXT Column?
  1. 1

    TEXT and BLOB columns cannot be fully indexed unless a prefix length is specified.

  2. 2

    MySQL requires something like INDEX(column(100)) for indexing.

  3. 3

    Only the specified prefix (e.g., first 100 characters) is included in the index.

  4. 4

    Full indexing is not allowed because values may be very large and variable-length.

  5. 5

    Indexing large TEXT/BLOB columns increases storage and reduces performance.

Key Behaviors to Know
  1. 1

    TEXT has collation-aware comparisons; BLOB does not.

  2. 2

    Prefix indexing helps performance but limits exact matching for long values.

  3. 3

    Full-text indexing (FULLTEXT INDEX) is recommended for large text search instead of regular indexes.

Difficulty: 7/10
Topics: storage format, sorting behavior, indexing limitations

Scenario Questions

0-2 years experience
  1. 1

    If you need to store user‑uploaded images in a MySQL table, would you use a BLOB column or a VARCHAR column? Explain how MySQL stores the data and what happens when you run ORDER BY on that column.

  2. 2

    You add a TEXT column to a table and then try to create a regular index on it. What error do you see, and why does MySQL reject it?

  3. 3

    When you SELECT a BLOB column without any WHERE clause, how does MySQL retrieve the data from disk? Describe the storage layout.

2-5 years experience
  1. 1

    Our service started to time out when sorting a table by a TEXT column. Walk me through why sorting TEXT is expensive and what MySQL does internally.

  2. 2

    We need to add a prefix index on a VARCHAR(255) that stores JSON strings. How would you decide the prefix length, and what are the trade‑offs compared to indexing the whole column?

  3. 3

    A query that filters on a TEXT column using LIKE '%foo%' is running slowly. Explain how MySQL stores TEXT values and why the index can't help, and propose a fix.

5-8 years experience
  1. 1

    Design a schema for logging large request bodies (up to 10 KB) that need to be searchable by a few keywords. How would you store the bodies, and how would you enable efficient search without violating MySQL’s index limits on TEXT?

  2. 2

    Our replication lag spikes when we start bulk‑loading millions of rows containing BLOB data. Explain the impact of MySQL’s internal storage of BLOB/TEXT on binary logging and replication, and suggest mitigation strategies.

  3. 3

    We want to migrate a legacy MySQL table that uses a TEXT column as a primary key to a new sharded architecture. Discuss the challenges around sorting, indexing, and data distribution.

8+ years experience
  1. 1

    Across multiple services we have dozens of tables with large TEXT columns that are occasionally indexed for full‑text search. Propose a long‑term strategy for handling storage, sorting, and indexing at scale, considering backup, migration, and future MySQL version changes.

  2. 2

    Our company is moving from MySQL to a distributed SQL database that does not support BLOB/TEXT indexing the same way. How would you plan the migration to preserve query semantics and performance?

  3. 3

    Explain how you would refactor a monolithic application that heavily relies on ordering by TEXT columns to avoid the performance pitfalls, while keeping the data model stable for downstream consumers.

Follow-up Questions

  • ❓What error does MySQL raise when you try to create a regular index on a TEXT column?
  • ❓How does the InnoDB row format (compact vs. dynamic) influence BLOB/TEXT storage?
  • ❓When would you choose a FULLTEXT index over a prefix index for a TEXT column?