MySQL vs Elasticsearch para búsqueda en ecommerce: por qué SQL colapsa con los filtros

Por qué la búsqueda en SQL colapsa con los filtros

La “búsqueda” en ecommerce no es una consulta única. Es una conversación entre una persona y tu catálogo:
escribe algo, constriñe, vuelve a constriñir, ordena, pagina y exige que la interfaz responda
en menos de unos pocos cientos de milisegundos mientras también muestra conteos de facetas. Cada clic añade restricciones,
y cada restricción multiplica las formas en que tu base de datos puede equivocarse.

Modo de fallo: dolor combinatorio, no una única consulta lenta

Con MySQL, el patrón típico es:

• Una consulta para obtener resultados (a menudo con joins para precio, inventario, categoría, atributos).
• Varias consultas para calcular conteos de facetas (conteos por marca, talla, color, etc.).
• Otra consulta para “quiso decir” o sinónimos o trucos de puntuación de relevancia.

Si haces todo eso en vivo contra tablas normalizadas, le pides a una base de datos transaccional
que se comporte como un motor de búsqueda. Lo intentará. También pondrá tus CPUs en llamas.

Qué hacen los “filtros” al SQL: destruyen las suposiciones de selectividad

Los índices B-tree (por defecto en InnoDB de MySQL) son geniales para reducir un conjunto de datos cuando
los predicados son selectivos y se alinean con el prefijo del índice. Los filtros en búsqueda facetada no se alinean educadamente.
Se ven como:

• WHERE category IN (...) (múltiples valores, a menudo poco selectivos en la cima del árbol)
• AND brand_id IN (...) (selección múltiple, controlada por el usuario)
• AND price BETWEEN ... (predicados de rango que matan el resto de índices compuestos)
• AND (title LIKE '%term%' OR description LIKE '%term%') (comodín inicial = el índice es decorativo)
• AND attr_id=... AND attr_value IN (...) repetido N veces (explosión de joins EAV)
• ORDER BY popularity DESC con paginación profunda (filesort + escaneo)

El optimizador de MySQL tiene que elegir un plan. Para predicados complejos, adivina, basándose en estadísticas
que pueden estar desactualizadas, ser gruesas o incorrectas para datos sesgados (que los catálogos de producto indudablemente son).
Cuando se equivoca, no se vuelve “un 10% más lento.” Se convierte en “timeouts y una reunión.”

La trampa de los “conteos de facetas”: estás ejecutando analítica sobre OLTP

Los conteos de facetas son básicamente agregaciones GROUP BY sobre un conjunto filtrado. Eso es trabajo analítico.
En SQL lo implementas con GROUP BY a través de tablas unidas, y o bien:

• Ejecutas una gran agregación por faceta (N consultas por petición), o
• Intentas hacer una mega-consulta con agregados condicionales y luego lloras cuando se vuelve inmantenible.

La tragedia es que cada consulta de faceta repite la mayor parte del trabajo: aplica los mismos filtros, examina filas similares,
une las mismas tablas y luego cuenta. Puedes cachear, pero los filtros son definidos por el usuario y la combinatoria
del cache explota a menos que tu tráfico o tu catálogo sean pequeños.

Una verdad operativa seca: MySQL es predecible; los filtros de usuario no

MySQL brilla cuando puedes predecir patrones de acceso e indexarlos. La búsqueda facetada es adversarial:
los usuarios generan consultas ad-hoc con combinaciones raras. MySQL puede manejar parte de ello,
pero el sobre de rendimiento colapsa rápido cuando agregas:

• joins múltiples para atributos
• filtros de rango + ordenación
• paginación profunda
• agregaciones de facetas
• múltiples idiomas, stemming/sinónimos/ajuste de relevancia

Chiste #1: Ejecutar búsqueda facetada sobre un esquema completamente normalizado es como remolcar un barco con una bicicleta—posible,
pero todos los que te miran están nerviosos.

Cómo MySQL ejecuta realmente tu búsqueda “simple”

Si quieres entender por qué la búsqueda en SQL colapsa, necesitas interiorizar para qué está optimizado MySQL:
búsquedas puntuales y escaneos de rango estrechos sobre índices con consistencia transaccional, en almacenes por fila, con índices B-tree.
Los motores de búsqueda se construyen alrededor de índices invertidos, doc values columnar-ish, conjuntos de filtros en caché y un modelo de puntuación.
Física diferente.

Índices B-tree: excelentes en “encuentra filas así”, mediocres en “calcula relevancia”

Un índice B-tree sobresale cuando el predicado reduce rápidamente el conjunto de candidatos. Tiene problemas cuando:

• Necesitas coincidir tokens dentro de texto sin pre-tokenizar (LIKE '%term%').
• Necesitas ordenación por relevancia (requiere puntuación, frecuencias de término, boosts por campo).
• Necesitas lógica con muchas OR que amplía el escaneo (brand IN (...) OR category IN (...)).
• Necesitas conteos agregados en muchas dimensiones (facetas).

FULLTEXT de MySQL ayuda, pero no resuelve el facetado

Los índices FULLTEXT de MySQL pueden proporcionar coincidencia por tokens para ciertos casos,
y en InnoDB son reales y útiles. Pero la búsqueda en ecommerce quiere más:

• Prefijos, fuzziness, tolerancia a errores tipográficos
• Sinónimos y “quiso decir”
• Ajuste de relevancia por campos (título vs descripción)
• Filtros estructurados que deben ser rápidos
• Conteos de facetas y “muéstrame cuántos artículos coinciden si hago clic aquí”

Incluso si FULLTEXT acierta la coincidencia textual, el trabajo de join y filtrado que sigue aún puede ser tu cuello de botella.
Terminas con una consulta en dos etapas: la coincidencia de texto produce IDs, luego unes filtros, luego ordenas. Cada etapa tiene sus propios
precipicios de rendimiento.

Esquemas EAV: el asesino silencioso

Los sistemas de atributos a menudo se implementan como EAV (entity-attribute-value): un producto tiene muchos atributos,
cada uno almacenado como filas. Esto es flexible y terrible para filtros de búsqueda. Una consulta como “talla=10 y color=negro”
se convierte en múltiples self-joins o subconsultas contra la tabla de atributos. En un catálogo grande, eso es una explosión de joins
con predicados de baja selectividad. El optimizador no siempre puede reordenar joins de forma rentable, y aun cuando puede,
los conjuntos de resultados intermedios crecen rápido.

Ordenación y paginación: “ORDER BY popularity” no es gratis

Ordenar es un segundo cuello de botella que aparece después de “arreglar” el filtrado. Si tu consulta selecciona muchos candidatos
y luego ordena por algo no cubierto por un índice, MySQL puede hacer un filesort. Eso no es literalmente “ordenar en disco”
siempre, pero puede derramar a disco y consume CPU y memoria.

La paginación profunda (LIMIT 20 OFFSET 2000) es particularmente desagradable: MySQL aún tiene que encontrar y descartar las primeras
2000 filas después de aplicar filtros y ordenación. Es trabajo desperdiciado por petición, y escala linealmente con el offset.

Estabilidad del plan de consulta: en producción “a veces” se vuelve “siempre”

La mayor molestia operativa no es que una consulta sea lenta. Es que la misma forma de consulta se vuelve lenta según:

• qué categoría se selecciona
• qué marcas se seleccionan
• hora del día (buffer pool caliente vs frío)
• sesgo de datos (una marca tiene 40% de los productos)
• frescura (productos nuevos no incluidos en estadísticas)

Ves el p95 de latencia subir, luego culpas a la red, luego a la aplicación, y después descubres que un usuario
hizo clic en un filtro “Todas las colores” implementado como IN (...hundreds of ids...). Siempre es la característica inocua de la interfaz.

Una cita que ha sido dolorosamente fiable en operaciones: “La esperanza no es una estrategia.” — Gene Kranz.

Cómo Elasticsearch abarata los filtros (y por qué no es magia)

Elasticsearch se construye sobre Lucene, que fue diseñado para búsqueda. Suena obvio y tautológico,
pero tiene consecuencias prácticas: la estructura de índice es un índice invertido para texto más doc values estilo columnar
para agregaciones y ordenación. Los filtros se convierten en operaciones de conjuntos sobre bitsets, no en rompecabezas de joins.

Índice invertido: la ventaja central

En un índice invertido, cada término apunta a una lista de postings con los IDs de documentos que lo contienen. Intersecta listas de postings,
aplica filtros como conjuntos precomputados, puntuación, y listo. La clave es que el trabajo escala con el número de coincidencias,
no con el número de filas escaneadas a través de tablas unidas.

Los filtros son cacheables y componibles

En Elasticsearch, un filtro (por ejemplo brand:Acme) suele convertirse en un bitset: un mapa de bits de documentos que coinciden.
Combina filtros vía AND/OR rápidamente. Muchos filtros pueden cachearse de forma efectiva porque se reutilizan entre peticiones,
especialmente facetas comunes como “en stock” o “envío gratis.”

Los conteos de facetas (agregaciones) son ciudadanos de primera clase. Elasticsearch puede calcular conteos por marca, talla, color, etc.,
usando doc values y optimizaciones a nivel de segmento. No estás escribiendo N GROUP BY diferentes y rezando
para que el optimizador no interprete tu esquema como un desafío personal.

La denormalización es el precio del rendimiento

Elasticsearch funciona mejor cuando cada producto es un documento único que contiene los campos por los que filtras y ordenas.
Eso significa denormalizar: incluir nombre de marca, ruta de categoría, atributos, estado de disponibilidad, quizá incluso rango computado.
Los ingenieros de OLTP a menudo resisten la denormalización por principio. La búsqueda te obliga a hacerlo de todos modos, pero en un sistema separado.

Control de realidad operativa: Elasticsearch te compra rendimiento, no simplicidad

Elasticsearch no es gratis:

• Las canalizaciones de indexación pueden fallar. Los backfills pueden tardar horas.
• Los errores de mapping son para siempre (o al menos hasta reindexar).
• Dimensionar el clúster es ingeniería real, no sensaciones.
• La consistencia es eventual por defecto; debes diseñar alrededor de ello.
• Ahora ejecutas dos sistemas: MySQL como verdad, Elasticsearch para búsqueda.

Pero si tu producto necesita facetas rápidas y relevancia, Elasticsearch es el tipo correcto de complejidad.
Encaja con la forma del problema.

Chiste #2: Elasticsearch es el amigo que te ayuda a mudarte rápido—luego te pide que lo ayudes a mover un clúster el próximo fin de semana.

Hechos interesantes y contexto histórico (lo que explica los tradeoffs actuales)

1. Lucene precede a Elasticsearch por casi una década. Lucene comenzó a finales de los 90; Elasticsearch llegó después como un wrapper distribuido con API.
2. InnoDB de MySQL se convirtió en motor por defecto en MySQL 5.5. Ese cambio importó: transacciones, recuperación ante fallos y comportamiento del buffer pool definen cómo se ve “rápido”.
3. FULLTEXT en InnoDB se añadió después de que MyISAM lo tuviera por años. La característica existe, pero el ecosistema de analizadores y puntuación en Lucene es más profundo.
4. La “búsqueda” temprana en ecommerce solía ser navegador por categoría más LIKE. Ese legado aún persigue esquemas: tablas EAV, tablas de palabras clave y columnas de relevancia hechas a mano.
5. La navegación facetada se volvió corriente con catálogos grandes en los 2000. Una vez que “filtrar por talla/color/marca” se volvió imprescindible, las bases OLTP comenzaron a ser abusadas.
6. Elasticsearch popularizó el “schema on write” para búsqueda. Tus decisiones de mapping al indexar afectan fuertemente el rendimiento y la corrección en tiempo de consulta.
7. Los doc values columnar fueron un punto de inflexión para las agregaciones. Sin doc values, “conteos por faceta” a escala serían mucho más caros.
8. Los clústeres de búsqueda distribuidos hicieron de la relevancia una preocupación de producción. No solo consultas; ajustas analizadores, sinónimos, stemming y puntuación entre shards.

Tres microhistorias corporativas desde las trincheras

1) Incidente causado por una suposición equivocada: “Está indexado, así que es rápido”

Un minorista mediano tenía un endpoint de búsqueda respaldado por MySQL que “funcionaba bien” en staging. En producción fue otra historia.
El endpoint unía productos con inventario, precios, categorías y atributos, luego ordenaba por popularidad. Habían
añadido índices por todas partes. Alguien incluso tenía una hoja de cálculo.

El tráfico subió durante una campaña estacional, y la latencia p95 de búsqueda pasó de “aceptable” a “puedes refrescar la página
y seguir esperando.” La CPU de la base de datos estaba alta pero no al 100%. El verdadero dolor fue E/S y la churn del buffer pool:
la consulta tocaba demasiadas páginas, de forma impredecible.

La suposición equivocada fue sutil: creyeron que porque cada tabla tenía un índice en las claves de join, el join
seguiría siendo barato bajo filtros. Pero los filtros estaban en atributos almacenados en filas EAV, así que el orden de join
cambiaba con diferentes filtros. El optimizador eligió un plan que parecía bueno para filtros comunes y catastrófico
para una categoría específica con gran diversidad de atributos.

La respuesta al incidente no fue glamurosa. Deshabilitaron temporalmente facetas de selección múltiple para esa categoría y forzaron
un subconjunto más estrecho de filtros. La latencia se recuperó de inmediato. La solución raíz fue mover el filtrado y el ranking
a Elasticsearch, dejando MySQL solo como catálogo de referencia.

La nota del postmortem que importó: “La presencia de un índice no es la utilidad del índice.” Si un predicado no es selectivo, tu
índice es una sugerencia, no una solución.

2) Optimización que salió mal: cachear los resultados SQL

Otra compañía intentó la jugada clásica: “Cacheemos las respuestas de búsqueda en Redis.” Hashearon los parámetros de la petición
y cachearon la respuesta JSON completa por 10 minutos. En papel redujo la carga. En la práctica creó nuevos modos de fallo.

El catálogo cambiaba con frecuencia: precios actualizados, inventario fluctuante, promociones iniciadas y terminadas. La caché sirvió
resultados obsoletos que violaban reglas de negocio (artículos fuera de stock mostrados como disponibles, precios con descuento faltantes).
Soporte al cliente lo notó. Marketing lo notó. El CFO lo notó.

Ajustaron el TTL. La carga volvió. Añadieron ganchos de invalidación de caché desde la canalización del catálogo. Se volvió un sistema
complejo y frágil basado en eventos, y aún no podían invalidar todas las combinaciones de filtros. La combinatoria los venció:
no puedes invalidar eficientemente “todas las consultas que incluyen la marca X y cualquier color y precio entre A y B.”

La solución final fue aburrida: dejar de cachear respuestas completas y en su lugar cachear subresultados estables (como bitsets de filtros) en
el motor de búsqueda, donde los primitivos están diseñados para ello. Introdujeron Elasticsearch para tiempo de consulta y usaron MySQL para
escrituras y autoridad. El cacheo de respuestas quedó solo para páginas de categoría de nivel superior con parámetros predecibles.

3) Práctica aburrida pero correcta que salvó el día: dual-write y indexación reproducible

Una organización más grande hizo algo poco fashion: trataron la indexación de búsqueda como una canalización de producción de primera clase.
Las actualizaciones de producto fluían a una cola durable. Un consumidor actualizaba Elasticsearch. Otro consumidor actualizaba MySQL.
Y cada evento era reproducible desde el almacenamiento.

Una tarde, se coló un bug de mapping: un campo numérico se indexó como texto. Los conteos de facetas se volvieron raros, la ordenación falló,
y alguien intentó arreglarlo cambiando el mapping. Elasticsearch educadamente se negó porque los mappings no son tan flexibles.
El equipo no entró en pánico. Ese es el punto de los procesos aburridos.

Crearon un índice nuevo con el mapping correcto, reprodujeron eventos desde el log durable, validaron conteos contra MySQL,
y cambiaron un alias. Evitaron downtime; el “radio de impacto” se limitó a rarezas temporales en relevancia durante el reindex.

Nada de esto fue ingenioso. Fue solo disciplina: log de eventos inmutable, indexación idempotente, cutovers con alias, y dashboards que comparan
documentos del índice de búsqueda con los conteos de la fuente de verdad. Cuando la búsqueda es una característica de producto,
así se ve la madurez.

Guía rápida de diagnóstico

Cuando la búsqueda se pone lenta, tu trabajo no es discutir cuál sistema es “mejor.” Tu trabajo es encontrar el cuello de botella en minutos,
no días, y tomar una decisión segura bajo presión.

Primero: clasifica la lentitud (app vs base de datos vs clúster de búsqueda)

• Revisa p95/p99 en el borde: ¿está lento todo el endpoint o solo formas de consulta específicas?
• Compara “sin filtros” vs “muchos filtros”: si los filtros causan un salto superlineal, es trabajo de plan/agregación.
• Revisa presupuestos de error: timeouts, 5xx, disparos de circuit breaker.

Segundo: aisla el trabajo en tiempo de consulta del trabajo de frescura de datos

• MySQL: ¿está lento por escaneos/ordenaciones, o porque estás bloqueado (locks)?
• Elasticsearch: ¿está lento por agregaciones pesadas, o porque el clúster está ocupado en merges / GC / atado a disco?

Tercero: busca los precipicios clásicos

• Precipicios de MySQL: filesort, tablas temporales, explosión de “rows examined”, joins EAV, paginación OFFSET profunda, estadísticas obsoletas.
• Precipicios de Elasticsearch: agregaciones de alta cardinalidad, demasiados shards, explosión de fielddata, discos lentos, presión de heap, contención por refresh/merge.

Cuarto: toma la mitigación de menor riesgo

• Deshabilita temporalmente la faceta o la opción de ordenación más problemática.
• Limita OFFSET (o exige “search after”).
• Reduce el tamaño de las agregaciones.
• Vuelve a una búsqueda solo por palabras clave sin conteos.

Quinto: decide arquitectura, no heroísmos

Si tu producto requiere filtrado rico + conteos de facetas + relevancia, mueve la carga de consultas a un motor de búsqueda.
Si tu producto es “búsqueda admin” con pocos filtros, MySQL está bien—solo hazlo intencionalmente.

Tareas prácticas con comandos, salidas y decisiones

Estas son las tareas que ejecuto en producción cuando alguien dice “la búsqueda está lenta.” Cada tarea incluye:
un comando ejecutable, una salida de ejemplo realista, lo que significa y la decisión que tomas.

Tarea 1: Identificar las sentencias MySQL lentas top (por tiempo total)

cr0x@server:~$ sudo pt-query-digest /var/log/mysql/mysql-slow.log | head -n 25
# 120s user time, 2s system time, 33.12M rss, 101.02M vsz
# Current date: Mon Dec 30 12:10:07 2025
# Hostname: db1
# Files: /var/log/mysql/mysql-slow.log
# Overall: 1.2k total, 37 unique, 0 QPS, 0x concurrency ____________
# Time range: 2025-12-30T10:00:00 to 2025-12-30T12:00:00
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          3500s   150ms     12s      3s      9s     2s   1.5s
# Rows examine        3.1G       0    6.0M   2.6M   5.9M   1.1M   2.1M
# Query 1: 45% 0.12 QPS, 0.40x concurrency, ID 0xA1B2C3D4 at byte 12345
# Scores: V/M = 0.98
# Time range: 2025-12-30T10:10:00 to 2025-12-30T11:55:00
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         22     270
# Exec time     45   1580s   800ms     12s      6s     10s     2s      5s
# Rows examine  62   1.9G   400k    6.0M   7.1M   5.9M   1.1M   6.8M

Qué significa: Tu “consulta de búsqueda” no es lenta; está escaneando millones de filas por petición y dominando el tiempo.

Decisión: Extrae esa forma de consulta, ejecuta EXPLAIN ANALYZE, y o rediseña índices/esquema o mueve la carga fuera de MySQL.

Tarea 2: Encontrar consultas MySQL en ejecución y su tiempo

cr0x@server:~$ mysql -e "SHOW FULL PROCESSLIST\G" | sed -n '1,35p'
*************************** 1. row ***************************
     Id: 91234
   User: app
   Host: 10.0.2.15:53912
     db: catalog
Command: Query
   Time: 18
  State: Sending data
   Info: SELECT p.id, p.title FROM products p
         JOIN product_attr pa ON pa.product_id=p.id
         WHERE p.category_id IN (12,13) AND pa.attr_id=7 AND pa.value IN ('black','navy')
         ORDER BY p.popularity DESC LIMIT 20 OFFSET 2000

Qué significa: “Sending data” durante 18 segundos a menudo significa escaneo + join + orden, no I/O de red.

Decisión: Mitigar la paginación profunda y examinar si el predicado de join es suficientemente selectivo para usar índices.

Tarea 3: Inspeccionar plan de consulta con métricas en tiempo de ejecución (MySQL 8)

cr0x@server:~$ mysql -e "EXPLAIN ANALYZE SELECT p.id FROM products p JOIN product_attr pa ON pa.product_id=p.id WHERE p.category_id IN (12,13) AND pa.attr_id=7 AND pa.value IN ('black','navy') ORDER BY p.popularity DESC LIMIT 20 OFFSET 2000;"
+----------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------+
| -> Limit/Offset: 20/2000  (cost=84512.23 rows=2020) (actual time=15123.412..15123.900 rows=20 loops=1)
    -> Sort: p.popularity DESC  (cost=84512.23 rows=40123) (actual time=15123.410..15123.650 rows=2020 loops=1)
        -> Nested loop inner join  (cost=40211.11 rows=40123) (actual time=112.310..15012.000 rows=420000 loops=1)
            -> Index range scan on p using idx_category (category_id in (12,13))  (cost=1123.00 rows=70000) (actual time=2.100..50.000 rows=68000 loops=1)
            -> Index lookup on pa using idx_attr (attr_id=7, value in ('black','navy')) (cost=0.50 rows=6) (actual time=0.200..0.210 rows=6 loops=68000) |
+----------------------------------------------------------------------------------------------------------------------------------+

Qué significa: 68k filas externas generan 68k búsquedas repetidas; los resultados intermedios explotan; ordenar 420k candidatos para descartar 2000 es trabajo desperdiciado.

Decisión: Evitar paginación OFFSET, precomputar/denormalizar atributos para filtros comunes, o mover filtrado/ordenación a Elasticsearch.

Tarea 4: Verificar definiciones de índices y utilidad de prefijos

cr0x@server:~$ mysql -e "SHOW INDEX FROM product_attr\G" | sed -n '1,35p'
*************************** 1. row ***************************
        Table: product_attr
   Non_unique: 1
     Key_name: idx_attr
 Seq_in_index: 1
  Column_name: attr_id
    Collation: A
  Cardinality: 32
*************************** 2. row ***************************
        Table: product_attr
   Non_unique: 1
     Key_name: idx_attr
 Seq_in_index: 2
  Column_name: value
    Collation: A
  Cardinality: 2100
*************************** 3. row ***************************
        Table: product_attr
   Non_unique: 1
     Key_name: idx_attr
 Seq_in_index: 3
  Column_name: product_id
    Collation: A
  Cardinality: 800000

Qué significa: Baja cardinalidad para attr_id sugiere muchas filas por atributo; el índice ayuda menos de lo que piensas bajo categorías amplias.

Decisión: Considera índices compuestos que coincidan con tus patrones de consulta más comunes, o remodela atributos (aplanar atributos calientes en columnas o en una tabla desnormalizada).

Tarea 5: Comprobar si MySQL derrama a disco por tablas temporales

cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Created_tmp%';"
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Created_tmp_disk_tables | 184203   |
| Created_tmp_files       | 9132     |
| Created_tmp_tables      | 512004   |
+-------------------------+----------+

Qué significa: Una alta proporción de tablas temporales en disco indica sorts/group-bys que no caben en memoria o que no pueden usar índices.

Decisión: Reduce la complejidad de GROUP BY, asegura índices que soporten el agrupamiento, o deja de hacer conteos de facetas en MySQL.

Tarea 6: Confirmar presión del buffer pool (InnoDB)

cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';"
+---------------------------------------+-----------+
| Variable_name                         | Value     |
+---------------------------------------+-----------+
| Innodb_buffer_pool_read_requests      | 982341223 |
| Innodb_buffer_pool_reads              | 18233411  |
+---------------------------------------+-----------+

Qué significa: Las lecturas del buffer pool (físicas) son significativas. Las consultas de búsqueda están moviendo páginas y fallando en la caché.

Decisión: O provisiona más memoria (alivio temporal) o reduce el conjunto de trabajo moviendo la búsqueda fuera de MySQL / denormalizando / cacheando de otra manera.

Tarea 7: Revisar MySQL por esperas de bloqueo que impacten la búsqueda

cr0x@server:~$ mysql -e "SELECT * FROM performance_schema.data_lock_waits\G" | sed -n '1,40p'
*************************** 1. row ***************************
REQUESTING_ENGINE_LOCK_ID: 139941888:106:4:5:12345
REQUESTING_ENGINE_TRANSACTION_ID: 812345
BLOCKING_ENGINE_LOCK_ID: 139941888:106:4:5:12345
BLOCKING_ENGINE_TRANSACTION_ID: 812100
REQUESTING_THREAD_ID: 2221
BLOCKING_THREAD_ID: 2199

Qué significa: La latencia de búsqueda puede provenir de contención de locks, a menudo debido a escrituras o transacciones largas que bloquean lecturas (o locks de metadata).

Decisión: Arregla la ruta de escrituras, acorta transacciones y separa réplicas de lectura para búsqueda si debes mantener búsqueda en SQL.

Tarea 8: Medir latencia de búsqueda en Elasticsearch y salud de shards

cr0x@server:~$ curl -s http://localhost:9200/_cat/health?v
epoch      timestamp cluster status node.total node.data shards pri relo init unassign pending_tasks max_task_wait_time active_shards_percent
1735560300 12:05:00  es-prod yellow          6         6   420 210    0    0       12             0                  -                 97.1%

Qué significa: Yellow con shards no asignados puede añadir latencia y riesgo, especialmente si faltan réplicas y los nodos están bajo presión.

Decisión: Restaura la capacidad de asignación de réplicas, arregla watermarks de disco, o reduce el número de shards antes de obsesionarte con el tuning de consultas.

Tarea 9: Identificar consultas lentas en Elasticsearch mediante profile

cr0x@server:~$ curl -s -H 'Content-Type: application/json' http://localhost:9200/products/_search -d '{
  "profile": true,
  "size": 20,
  "query": {
    "bool": {
      "must": [{"match": {"title": "shoes"}}],
      "filter": [
        {"terms": {"brand_id": [12, 15, 18]}},
        {"term": {"in_stock": true}},
        {"range": {"price": {"gte": 50, "lte": 120}}}
      ]
    }
  },
  "aggs": {"brands": {"terms": {"field": "brand_id"}}}
}' | sed -n '1,35p'
{
  "took" : 38,
  "timed_out" : false,
  "_shards" : { "total" : 12, "successful" : 12, "skipped" : 0, "failed" : 0 },
  "hits" : { "total" : { "value" : 18432, "relation" : "eq" }, "hits" : [ ... ] },
  "profile" : { "shards" : [ { "searches" : [ { "query" : [ { "type" : "BooleanQuery", "time_in_nanos" : 8200000 } ] } ] } ] }
}

Qué significa: took: 38ms está bien; el profiling muestra dónde se va el tiempo (consulta vs agregación vs fetch).

Decisión: Si las agregaciones dominan, reduce cardinalidad, usa aggs de filter, o precomputea algunas facetas.

Tarea 10: Detectar shards en exceso (un fallo fácil en Elasticsearch)

cr0x@server:~$ curl -s http://localhost:9200/_cat/indices/products?v
health status index    uuid                   pri rep docs.count docs.deleted store.size pri.store.size
green  open   products Zk8p3bYfQkOaK9mD1n2x3Q  24   1   820000        12000     96gb        48gb

Qué significa: 24 shards primarios para <1M docs suele ser excesivo. Oversharding aumenta overhead y latencia.

Decisión: Reindexar en menos shards, o usar rollover/ILM si realmente necesitas índices por tiempo (la mayoría de catálogos de producto no lo requieren).

Tarea 11: Revisar indicadores de presión de heap JVM en Elasticsearch

cr0x@server:~$ curl -s http://localhost:9200/_nodes/stats/jvm?filter_path=nodes.*.jvm.mem.heap_used_percent,nodes.*.name | sed -n '1,25p'
{
  "nodes" : {
    "9a1b" : { "name" : "es-data-1", "jvm" : { "mem" : { "heap_used_percent" : 78 } } },
    "9a1c" : { "name" : "es-data-2", "jvm" : { "mem" : { "heap_used_percent" : 83 } } }
  }
}

Qué significa: Uso sostenido de heap alto se correlaciona con pausas de GC y picos de latencia, especialmente con agregaciones pesadas.

Decisión: Reduce carga de agregaciones, arregla mappings para evitar fielddata, escala nodos de datos o ajusta el tamaño del heap cuidadosamente.

Tarea 12: Validar mappings para campos de filtro (keyword vs text)

cr0x@server:~$ curl -s http://localhost:9200/products/_mapping | sed -n '1,45p'
{
  "products" : {
    "mappings" : {
      "properties" : {
        "brand" : { "type" : "text" },
        "brand_id" : { "type" : "integer" },
        "color" : { "type" : "keyword" },
        "title" : { "type" : "text" },
        "in_stock" : { "type" : "boolean" },
        "price" : { "type" : "scaled_float", "scaling_factor" : 100 }
      }
    }
  }
}

Qué significa: Filtrar/agrupar por brand como text es una trampa; quieres keyword (o usar subcampo brand.keyword).

Decisión: Arregla el mapping en un índice nuevo y reindexa. No “trabajes alrededor” con scripts en tiempo de ejecución a menos que disfrutes la latencia.

Tarea 13: Verificar frescura de estadísticas de MySQL (cordura del optimizador)

cr0x@server:~$ mysql -e "ANALYZE TABLE products, product_attr;"
+-------------------+---------+----------+----------+
| Table             | Op      | Msg_type | Msg_text |
+-------------------+---------+----------+----------+
| catalog.products  | analyze | status   | OK       |
| catalog.product_attr | analyze | status | OK       |
+-------------------+---------+----------+----------+

Qué significa: Refrescar estadísticas puede cambiar planes de consulta dramáticamente si estaban obsoletos.

Decisión: Si esto “arregla” la búsqueda, aún necesitas un plan durable: programa analyze, o rediseña consultas para que sean robustas.

Tarea 14: Detectar abuso de paginación profunda en la capa de aplicación

cr0x@server:~$ sudo awk '{print $7}' /var/log/nginx/access.log | grep -E 'search\?' | sed 's/.*offset=\([0-9]\+\).*/\1/' | sort -n | tail -n 10
2000
2400
3000
3600
4200
5000
6200
8000
12000
20000

Qué significa: Usuarios (o bots) solicitan offsets que causan trabajo lineal en MySQL y trabajo no trivial en motores de búsqueda.

Decisión: Limita el offset, exige paginación basada en cursor y aplica mitigación para bots. La paginación profunda es una decisión de producto disfrazada de técnica.

Tarea 15: Comprobar latencia de disco (dependencia oculta de ambos sistemas)

cr0x@server:~$ iostat -x 1 3
Linux 6.5.0 (db1)  12/30/2025  _x86_64_  (32 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           12.1    0.0     3.2    18.7    0.0    66.0

Device            r/s     w/s   rkB/s   wkB/s  await  svctm  %util
nvme0n1         120.0   340.0  9800.0 40200.0  18.4   1.2   92.0

Qué significa: Alto await y %util cercano a saturación significan que la latencia de almacenamiento te está limitando. Las cargas de búsqueda amplifican lecturas aleatorias.

Decisión: Si el disco es el limitador, ninguna cantidad de ajuste de consultas te salvará completamente. Reduce E/S (mejores índices/denormalización) o mejora el almacenamiento.

Errores comunes: síntomas → causa raíz → solución

1) Síntoma: “La búsqueda es rápida en algunas categorías, terrible en otras”

Causa raíz: Sesgo de datos + estadísticas desactualizadas/inexactas llevan a planes de consulta inestables en MySQL; ciertas categorías explotan la cardinalidad de joins.

Solución: Ejecuta EXPLAIN ANALYZE en categorías representativas; refresca estadísticas; añade índices compuestos dirigidos; considera mover el facetado a Elasticsearch.

2) Síntoma: “Agregar un filtro más hace que la latencia salte de 200ms a 8s”

Causa raíz: El predicado cambia el orden de joins o desactiva el uso de índices; un predicado de rango u OR rompe el prefijo de un índice compuesto; aparecen tablas temporales/filesort.

Solución: Reescribe la consulta para reducir ORs; evita LIKE con comodín inicial; aplana atributos calientes; deja de usar OFFSET; o descarga a Elasticsearch el contexto de filtros.

3) Síntoma: “Los conteos de facetas son incorrectos o faltan bajo carga”

Causa raíz: Cachear respuestas completas con inventario/precio obsoletos; o ejecutar conteos en réplicas con lag; o mezclar índice de búsqueda eventual con stock en tiempo real.

Solución: Decide qué debe ser fuertemente consistente (usualmente checkout, no búsqueda). Usa indexación casi en tiempo real y separa la lógica de “disponibilidad” con SLAs claros.

4) Síntoma: “La CPU de MySQL está bien pero el tiempo de respuesta es terrible”

Causa raíz: Espera de I/O de disco por scans grandes, mala localidad o misses en buffer pool; ordenaciones que derraman a disco.

Solución: Mide Innodb_buffer_pool_reads y latencia de disco; reduce filas examinadas; provisiona memoria/almacenamiento; deja de hacer facetado analítico en MySQL.

5) Síntoma: “Elasticsearch está lento después de añadir más campos al índice”

Causa raíz: Explosión de mapping; agregaciones de alta cardinalidad; demasiados campos keyword; incremento de doc values y huella de disco.

Solución: Audita mappings; indexa solo lo que consultas; deshabilita indexado para campos no buscados; prefiere IDs numéricos para facetas; reduce tamaños de agregación.

6) Síntoma: “El clúster de Elasticsearch parece sano pero las consultas hacen timeout”

Causa raíz: Oversharding, merges lentos, presión de heap o discos cerca de saturación; el fan-out de consultas multiplica el overhead por shard.

Solución: Reduce count de shards vía reindex; mejora almacenamiento; vigila heap y GC; ajusta refresh/merge; escala nodos de datos.

7) Síntoma: “Usamos MySQL FULLTEXT y aún no hace lo que necesitamos”

Causa raíz: FULLTEXT gestiona coincidencia de tokens, pero el filtrado estructurado + conteos de facetas + ajuste de relevancia siguen empujándote hacia un motor de búsqueda.

Solución: Trata FULLTEXT como una herramienta parcial para casos limitados. Si necesitas facetado y relevancia, usa Elasticsearch (u otro motor de búsqueda dedicado).

8) Síntoma: “El orden de resultados cambia entre peticiones”

Causa raíz: Desempates no deterministas en ordenaciones SQL; actualizaciones concurrentes; en Elasticsearch, empates de puntuación y merges de segmentos pueden reordenar puntuaciones iguales.

Solución: Añade ordenaciones secundarias estables (por ejemplo product_id), usa claves de orden deterministas y sé explícito sobre el comportamiento de ordenación.

Listas de verificación / plan paso a paso

Cuando MySQL es aceptable para “búsqueda” (haz esto, no aquello)

• Usa MySQL para búsqueda administrativa, herramientas de atención al cliente y catálogos pequeños con filtros simples.
• Evita joins EAV en rutas críticas. Si debes filtrar por atributos, precomputar una tabla desnormalizada con clave product_id.
• Mantén consultas amigables con índices: no LIKE con comodín inicial; limita la lógica OR; evita OFFSET profundo.
• Mide filas examinadas y tablas temporales; no confíes en “tiene un índice”.

Cuando Elasticsearch es la decisión correcta (la mayoría de frontend ecommerce)

• Necesitas navegación facetada con conteos.
• Necesitas ranking por relevancia y ajuste por campos.
• Necesitas tolerancia a faltas tipográficas, sinónimos, stemming y analizadores multi-idioma.
• Necesitas baja latencia consistente bajo combinaciones de filtros impredecibles.

Plan de migración paso a paso: MySQL como verdad, Elasticsearch para búsqueda

1. Define el contrato: qué campos son buscables, filtrables, ordenables; qué frescura garantizas.
2. Diseña el modelo de documento: un documento por producto con campos desnormalizados y atributos anidados si hacen falta.
3. Crea mappings intencionalmente: keyword para coincidencias exactas/facetas, text para búsqueda analizada, numeric para rangos, scaled_float para precios.
4. Construye una canalización de indexación: eventos en cola, actualizaciones idempotentes, lógica de reintento, dead-letter.
5. Backfill seguro: construye un índice nuevo, carga masiva, valida conteos y consultas de muestra, luego haz cutover con alias.
6. Implementa lecturas duales durante el despliegue: compara resultados MySQL vs ES para una porción del tráfico y atrapa drift de lógica/mapping.
7. Guardarraíles: limita agregaciones, profundidad de página, tasa para bots y registra formas de consulta.
8. Opéralo como producción: dashboards para heap/disco/latencia, alertas en lag de indexación, runbooks para reindex.

Reglas estrictas que previenen incidentes recurrentes

• Nunca lances una nueva faceta sin pruebas de carga con combinaciones realistas.
• Nunca permitas que la UI genere listas IN (...) sin límite.
• Siempre trata la paginación profunda como una decisión de producto con un límite explícito.
• Siempre mantén MySQL como fuente de verdad; el índice de búsqueda es una proyección.
• Siempre haz que el reindex sea repetible (aliases + origen reproducible).

Preguntas frecuentes

1) ¿Puedo mantener todo en MySQL si añado más índices?

Para búsquedas simples, sí. Para búsqueda facetada en ecommerce con muchos filtros y conteos, no. Los índices pueden reducir el dolor,
pero no cambian el desajuste fundamental: joins + group-bys + ranking no son el entorno feliz de MySQL a escala.

2) ¿Y si uso MySQL FULLTEXT?

FULLTEXT puede ayudar con coincidencia de tokens, pero el facetado y el ajuste de relevancia aún tienden a empujarte hacia un motor de búsqueda.
FULLTEXT tampoco te rescatará de joins EAV, paginación profunda y conteos de facetas caros.

3) ¿Por qué los filtros hacen que SQL sea más lento que solo búsqueda de texto?

Porque los filtros a menudo obligan a joins (atributos, inventario, tablas de precio) y matan la selectividad del índice. El conjunto candidato sigue siendo grande,
luego la ordenación y la paginación multiplican el trabajo. En Elasticsearch, muchos filtros se convierten en intersecciones de conjuntos cacheadas.

4) ¿Es Elasticsearch siempre más rápido?

Para cargas estilo búsqueda (texto + filtros + agregaciones), generalmente sí. Para búsquedas transaccionales y consistencia estricta, no.
Elasticsearch también puede ser más lento si sobremapas, mal distribuyes shards o ejecutas agregaciones pesadas en hardware débil.

5) ¿Cómo mantengo el inventario “en tiempo real” si Elasticsearch es eventualmente consistente?

Decide qué significa “en tiempo real” para la UX. Patrones comunes: indexar inventario con actualizaciones frecuentes; tratar “en stock” como casi en tiempo real;
o validar disponibilidad en add-to-cart/checkout. No finjas que los resultados de búsqueda son la verdad final.

6) ¿Cuál es el mayor error de esquema que cometen en Elasticsearch para ecommerce?

Mapear campos de faceta como text en lugar de keyword, e indexar todo “por si acaso.”
Inflan doc values, ralentizan agregaciones y hacen el uso de heap muy variable.

7) ¿Cómo evito problemas de paginación profunda?

Prefiere paginación basada en cursor. En MySQL, usa el “método seek” (recuerda la última clave de orden + id). En Elasticsearch, usa
search_after para ordenaciones estables. Además: limita la profundidad máxima de páginas. Nadie compra la página 500.

8) ¿Puedo hacer un híbrido: filtrar primero en MySQL y luego en Elasticsearch?

Puedes, pero suele ser un impuesto de complejidad con beneficios débiles. Si MySQL proporciona un conjunto de candidatos pequeño (muy selectivo),
entonces sí. Pero la mayoría de filtros de storefront no son suficientemente selectivos y terminas haciendo dos consultas caras en lugar de una.

9) ¿Cuál es el mínimo operativo para ejecutar Elasticsearch razonablemente?

Discos sólidos, suficiente heap, conteo de shards sensato, monitorización de heap/disco/latencia y una estrategia de reindex usando aliases.
Además: una canalización de indexación con reintentos y forma de detectar lag.

10) ¿Cuándo no debería usar Elasticsearch?

Si tu “búsqueda” es realmente una UI administrativa pequeña, o tu catálogo es diminuto y estable, MySQL es más simple.
Si tu equipo no puede operar un sistema distribuido, invierte en esa capacidad o elige una solución gestionada.

Conclusión: próximos pasos prácticos

Si la búsqueda de tu ecommerce incluye múltiples filtros, conteos de facetas, ranking por relevancia y cambios frecuentes de ordenación,
deja de intentar resolver el problema con más índices en MySQL. Pasarás semanas puliendo milisegundos hasta que una nueva faceta reintroduzca segundos.
Eso no es ingeniería; es negación con métricas.

Haz esto en su lugar:

1. Mide: captura formas de consulta lentas, filas examinadas, tablas tmp y latencia de disco.
2. Pon guardarraíles en la UI: limita paginación profunda, controla explosión de multi-selección y tasa a bots.
3. Decide el contrato: qué debe ser fresco, qué puede ser casi en tiempo real y qué significa corrección.
4. Mueve la búsqueda a Elasticsearch cuando el conjunto de funcionalidades lo demande: facetas, relevancia, tolerancia a errores tipográficos.
5. Opera la canalización: indexación reproducible, cutovers con alias, dashboards para lag de indexación y salud del clúster.

MySQL sigue siendo el lugar correcto para pedidos, verdad de inventario, reglas de precios y todo lo que explicarás a los auditores.
Elasticsearch es donde permites a los clientes explorar tu catálogo rápido sin convertir tu base de datos en una estufa eléctrica.