Elasticsearch Data Types & MySQL Mapping: Complete Guide with Pitfalls

Interview Focus Areas

Foundation mastery : Data types are the bedrock of mapping. Interviewers want to know if you have actually created indices and written DSL, not just "touched" ES in a project.

Selection capability : Choosing text vs keyword incorrectly, or using double for monetary amounts, causes real production incidents. Type selection directly reveals your practical depth.

Knowledge transfer : The most common scenario is syncing MySQL data to ES for search. Accurately translating MySQL concepts and field types into ES tests your understanding of both storage systems; rote memorization fails under follow-up questions.

Core Answer: ES Data Type Categories

ES data types fall into five categories:

Core Types : text, keyword — String duo: one tokenizes, one does not

Numeric Types : long, integer, short, byte, double, float, half_float, scaled_float — Four integer tiers, five floating-point tiers

Date Type : date — Stores both timestamps and formatted strings

Boolean Type : boolean — Simply true/false

Complex Types : object, nested, join, geo_point, ip, dense_vector, etc. — Objects, nested, parent-child, geo, vector

Concept Mapping: MySQL → Elasticsearch

Database → Index : ES 7.0+ removed Type concept

Table → Type (deprecated) → now directly Index : Old interview "Type" is historical artifact

Row → Document : One row = one JSON document

Column → Field : A field inside the document

Schema → Mapping : Defines field names and types

SQL → Query DSL : Query language

Deep Dive

1. Understanding ES-Specific text and keyword

This is the biggest difference from MySQL and a interview focal point. MySQL's VARCHAR serves both exact match ( WHERE name = 'phone') and fuzzy query ( LIKE '%phone%') in one field. ES splits this into two types:

PUT /product
{
  "mappings": {
    "properties": {
      "productName": {
        "type": "text",
        "analyzer": "ik_max_word",
        "fields": {
          "keyword": {
            "type": "keyword",
            "ignore_above": 256
          }
        }
      }
    }
  }
}

Breakdown:

text : Tokenizes. Writing "Xiaomi foldable phone" splits into terms "Xiaomi", "foldable", "phone" for inverted index. Suits full-text search via match queries.

keyword : No tokenization; entire string enters index as-is. Suits exact match ( term query), sorting, aggregations. Default ignore_above is 256 — content exceeding 256 chars won't be indexed, a classic pitfall.

fields sub-field : Main field uses text for search; sub-field uses keyword for sorting/aggregation. Query with productName.keyword for exact match. One definition, two use cases.

Remember: Search → text ; exact match, sort, aggregate → keyword . Most production string fields use this combo.

2. Concept Mapping: From MySQL to ES

Database → Index : One DB maps to one index; logical isolation same. But ES 7.0 removed Type (the old "Table maps to Type") because Types shared one Lucene index, causing field interference across Types — a design flaw removed entirely in 8.x.

Row → Document : MySQL row = record; ES document = JSON, schema-free (fields can differ per document).

Column → Field : Field concept aligns, but ES field types cannot change after index creation (Lucene segments are immutable). Changing requires reindex — cost is orders of magnitude higher than MySQL's ALTER TABLE.

3. Field Type Mapping Table (Key to Memorize)

BIGINT → long : Maps directly to Java Long INT → integer : 32-bit, maps to Java Integer SMALLINT / TINYINT → short / byte : Rarely used, just know them

DOUBLE → double : 64-bit double precision

FLOAT → float : 32-bit single precision

DECIMAL → scaled_float : ⚠️ Critical — detailed below

VARCHAR / TEXT → text + keyword sub-field : Need both search and exact match

Enum, status fields → keyword : No tokenization needed → direct keyword DATETIME / TIMESTAMP → date : Supports multiple formats

TINYINT(1) / BOOLEAN → boolean : —

BLOB → binary : Base64 encoded, not searchable

JSON (5.7+) → object / nested : One-to-many must use nested; object flattens arrays causing query errors

POINT (spatial) → geo_point : ES geo search far stronger than MySQL spatial index

— → ip , dense_vector : ES specialties, no direct MySQL equivalent

Critical: DECIMAL → scaled_float pitfall. ES lacks a true decimal-precise type; double has precision issues (classic 0.1 + 0.2 ≠ 0.3). scaled_float uses a scaling_factor (e.g., 100), stores as long (price 19.99 → 1999), divides on display — saves space and stays precise. For order amounts: either scaled_float or store cents as long; never raw double.

4. Dynamic Mapping Traps

If you create an index without explicit mapping, ES infers types (Dynamic Mapping). Rules:

JSON integer → long (not integer)

JSON float → float (not double) true / false → boolean String → text + keyword sub-field

Date-formatted string → date (date detection enabled by default via date_detection)

The trap: Suppose a field holds product codes. First few documents look like dates (e.g., "2024-01-15"), ES infers date. Later a normal code "SP001" arrives → write fails. Production rule: explicitly define mapping, disable unwanted auto-detection ; don't leave type inference to chance.

High-Frequency Interview Follow-Ups

text vs keyword difference? Must-answer. Tokenized vs not, match vs term, inverted index construction — nail these three points.

Why did ES 7.0 remove Type? Different Types shared one Lucene index; fields interfered (Type A field is date, Type B same-name field forced to date). Design was flawed; officially removed.

nested vs object difference? object flattens nested arrays, losing object boundaries — query "color=red AND size=XL" incorrectly matches "red-L + blue-XL"; nested indexes each nested object as separate document, preserves boundaries, trade-off: slower queries, more complex updates.

How to sync MySQL data to ES? Dual-write, Canal binlog listening, Flink CDC, Logstash scheduled pulls — each has pros/cons; comparing them scores bonus points.

Why can't ES field types change after definition? Lucene segments are immutable; inverted index structure fixed at creation. Type change requires new index + _reindex migration.

Common Interview Variants

Variant 1: "Difference between text and keyword, when to use each?"

Variant 2: "MySQL LIKE '%xx%' vs ES full-text search difference?" (Point: B-tree can't use leading wildcard → full scan vs inverted index)

Variant 3: "Why ES when MySQL exists? How to choose?"

Memory Mnemonics

Type selection : Search → text; query/sort/aggregate → keyword; integers → long; decimals → scaled; dates → date; one-to-many → nested.

Concept mapping : DB→Index, Row→Document, Column→Field, Schema→Mapping — Table gone, Mapping takes over.

Summary

Remember ES data types by five categories: core, numeric, date, boolean, complex. Focus on text / keyword combo and scaled_float for money. Map MySQL concepts via Database→Index, Row→Document, Column→Field, Schema→Mapping, and recall Type is dead. Cite two or three production pitfalls ( keyword 256 limit, dynamic mapping mis-inferring date, object flattening) and your interview rating jumps a tier.