sql - semantica - indices fulltext
¿Cuándo debería usar la indexación de texto completo? (4)
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 conteo (*) con CONTAINS (columna, "palabraB")
- tamaño del resultado varios cientos de miles
- tamaño del catálogo aprox 1.8GB
El índice de texto completo estaba en el rango de 2 s mientras que ''% wordB%'' estaba en el rango de 1-2 minutos.
¡Pero esto cuenta solo si no usa ningún criterio de selección adicional! Por ejemplo, si utilicé un prefijo "me gusta" en una columna de clave principal adicionalmente, el rendimiento fue peor ya que la operación de ir al índice de texto completo cuesta más que hacer una búsqueda de cadenas en algunos campos (siempre que no sean demasiado).
Por lo tanto, recomendaría el índice de texto completo solo en los casos en que tenga que hacer una "búsqueda de cadenas gratuita" o utilizar algunas de las características especiales de la misma ...
Tenemos un montón de consultas que "buscan" clientes, clientes, etc. Puedes buscar por nombre, correo electrónico, etc. Estamos utilizando declaraciones LIKE de la siguiente manera:
SELECT *
FROM customer
WHERE fname LIKE ''%someName%''
¿Ayuda la indexación de texto completo en el escenario? Estamos usando SQL Server 2005.
Dependerá de su DBMS. Creo que la mayoría de los sistemas no aprovecharán el índice de texto completo a menos que use las funciones de texto completo. (por ejemplo, MATCH/AGAINST en mySQL o FREETEXT / CONTAINS en 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: Descripción de la indexación de texto completo de SQL Server
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 usa LIKE , especialmente cuando busca con un % al comienzo de su comparación, SQL Server necesita realizar un escaneo de tabla de cada fila y un byte por verificación de bytes de la columna que está verificando.
FTS tiene algunos algoritmos mejores para hacer coincidir los datos, al igual que algunas estadísticas mejores sobre variaciones de nombres. Por lo tanto, FTS puede proporcionar un mejor rendimiento para emparejar a Smith, Smythe, Smithers, etc. cuando busca a Smith.
Sin embargo, es un poco más complejo usar FTS, ya que tendrá que dominar CONTAINS frente a FREETEXT y el formato arcano de la búsqueda. Sin embargo, si desea hacer una búsqueda donde coincidan FName o LName, puede hacerlo con una declaración en lugar de una OR.
Para determinar si FTS va a ser efectivo, determine cuántos datos tiene. Utilizo FTS en una base de datos de varios cientos de millones de filas y eso es un beneficio real sobre buscar con LIKE , pero no lo uso en todas las tablas.
Si el tamaño de su tabla es más razonable, menos de unos pocos millones, puede obtener una velocidad similar al crear un índice para cada columna en la que va a realizar búsquedas y SQL Server debe realizar una exploración de índice en lugar de una exploración de tabla.
Para responder la pregunta específicamente para MSSQL, la indexación de texto completo NO ayudará en su escenario.
Para mejorar esa consulta, puede hacer una de las siguientes cosas:
- Configure un catálogo de texto completo en la columna y use la función CONTAINS ().
Si estaba buscando principalmente con un prefijo (es decir, haciendo coincidir desde el inicio del nombre), podría cambiar el predicado a lo siguiente y crear un índice sobre la columna.
donde fname como ''prefijo%''
(1) es probablemente excesivo para esto, a menos que la ejecución de la consulta sea un gran problema.