¿Cuándo debe utilizar la indexación de texto completo?

Tenemos un montón de consultas que "buscan" clientes, clientes, etc. Puede buscar por nombre, correo electrónico, etc. Estamos usando COMO declaraciones de la siguiente manera: 

SELECT * 
FROM customer 
WHERE fname LIKE '%someName%'

¿La indexación de texto completo ayuda en el escenario? Estamos usando SQL Server 2005.

Author: marc_s, 2008-09-12
Source

4 answers

Dependerá de su DBMS. Creo que la mayoría de los sistemas no aprovecharán el índice de texto completo a menos que utilice las funciones de texto completo. (e. g. MATCH / AGAINST in MySQL or FREETEXT / CONTAINS in MS SQL)

Aquí hay un buen artículo sobre cuándo, por qué y cómo usar la indexación de texto completo en SQL Server: Comprensión de la Indexación de texto completo de SQL Server

 30
Author: Prestaul,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2008-09-11 23:42:08

FTS puede ayudar en este escenario, la pregunta es si vale la pena o no.

Para empezar, veamos por qué LIKE puede no ser la búsqueda más efectiva. Cuando se utiliza LIKE, especialmente cuando se está buscando con un % al comienzo de la comparación, SQL Server necesita realizar un análisis de tabla de cada fila y una comprobación byte por byte de la columna que se está comprobando.

FTS tiene algunos mejores algoritmos para emparejar datos al igual que algunos mejores estadísticas sobre variaciones de nombres. Por lo tanto, FTS puede proporcionar un mejor rendimiento para emparejar Smith, Smythe, Smithers, etc. cuando busca Smith.

Sin embargo, es un poco más complejo usar FTS, ya que necesitarás dominar CONTAINS vs FREETEXT y el formato arcano de la búsqueda. Sin embargo, si desea hacer una búsqueda donde FName o LName coincidan, puede hacerlo con una instrucción en lugar de una OR.

Para determinar si FTS va a ser eficaz, determinar la cantidad de datos que tener. Uso FTS en una base de datos de varios cientos de millones de filas y eso es un beneficio real sobre la búsqueda con LIKE, pero no lo uso en todas las tablas.

Si el tamaño de la tabla es más razonable, menos de unos pocos millones, puede obtener una velocidad similar creando un índice para cada columna en la que vaya a buscar y SQL Server debería realizar un análisis de índice en lugar de un análisis de tabla.

 20
Author: Josef,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-10-12 01:04:08

De acuerdo con mi escenario de prueba:

  • SQL Server 2008
  • 10.000.000 filas cada una con una cadena como " wordA wordB wordC..."(varía entre 1 y 30 palabras)
  • seleccionando count(*) con CONTAINS(columna, "wordB")
  • tamaño del resultado varios cientos de miles
  • tamaño del catálogo aprox 1.8 GB

El índice de texto completo estaba en el rango de 2s mientras que como '% wordB %' estaba en el rango de 1-2 minutos.

Pero esto cuenta solo si no usas ninguna criterios de selección adicionales! Por ejemplo, si utilizo algunos "like 'prefix%'" en una columna de clave primaria, además, el rendimiento fue peor ya que la operación de entrar en el índice de texto completo cuesta más que hacer una búsqueda de cadenas en algunos campos (siempre que no sean demasiado).

Así que recomendaría full-text index solo en los casos en los que tenga que hacer una "búsqueda de cadena libre" o utilizar algunas de las características especiales de la misma...

 6
Author: Strinder,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-03-18 10:16:42

Para responder la pregunta específicamente para MSSQL, la indexación de texto completo NO ayudará en su escenario.

Para mejorar esa consulta puedes hacer una de las siguientes cosas:

  1. Configure un catálogo de texto completo en la columna y use la función CONTAINS ().

  2. Si buscara principalmente con un prefijo (es decir, coincidiendo desde el comienzo del nombre), podría cambiar el predicado a lo siguiente y crear un índice sobre la columna.

    Donde fname como 'prefix%'

(1) es probablemente excesivo para esto, a menos que el rendimiento de la consulta sea un gran problema.

 3
Author: Brannon,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2008-09-12 00:00:13