segundo - optimizar consultas lentas mysql

Uso de SQL para determinar estadísticas de conteo de palabras de un campo de texto

Question

Recientemente he estado trabajando en algunas funciones de búsqueda de bases de datos y quería obtener información como el promedio de palabras por documento (por ejemplo, campo de texto en la base de datos) Lo único que he encontrado hasta ahora (sin procesar en el idioma de elección fuera de la base de datos) es:

SELECT AVG(LENGTH(content) - LENGTH(REPLACE(content, '' '', '''')) + 1)
FROM documents

Esto parece funcionar * pero tienes otras sugerencias? Actualmente estoy usando MySQL 4 (espero pasar a la versión 5 para esta aplicación pronto), pero también estoy interesado en soluciones generales.

¡Gracias!

* Me puedo imaginar que esta es una forma bastante aproximada de determinar esto, ya que no tiene en cuenta el HTML en el contenido y similares. Eso está bien para este proyecto en particular, pero una vez más, ¿hay mejores maneras?

Actualización: Para definir lo que quiero decir con "mejor": más preciso, rendimiento más eficiente o más correcto (fácil de mantener, buenas prácticas, etc.). Para el contenido que tengo disponible, la consulta anterior es lo suficientemente rápida y precisa para este proyecto, pero es posible que necesite algo similar en el futuro (así que pregunté).

Answer 1

Esto es bastante más rápido, aunque un poco menos preciso. Lo encontré con un 4% de luz en el conteo, lo que está bien para los escenarios de "estimación".

SELECT
    ROUND (   
        (
            CHAR_LENGTH(content) - CHAR_LENGTH(REPLACE (content, " ", "")) 
        ) 
        / CHAR_LENGTH(" ")        
    ) AS count    
FROM documents

Answer 2

Las capacidades de manejo de texto de MySQL no son lo suficientemente buenas para lo que quieres. Una función almacenada es una opción, pero probablemente será lenta. Su mejor apuesta para procesar los datos dentro de MySQL es agregar una función definida por el usuario . Si vas a construir una versión más nueva de MySQL de todos modos, también puedes agregar una función nativa .

La forma "correcta" es procesar los datos fuera de la base de datos ya que las bases de datos son para almacenamiento, no para procesamiento, y cualquier procesamiento pesado puede suponer una carga excesiva para el DBMS. Además, calcular el recuento de palabras fuera de MySQL hace que sea más fácil cambiar la definición de lo que cuenta como una palabra. ¿Qué tal almacenar el recuento de palabras en la base de datos y actualizarlo cuando se modifica un documento?

Ejemplo de función almacenada:

DELIMITER $$
CREATE FUNCTION wordcount(str LONGTEXT)
       RETURNS INT
       DETERMINISTIC
       SQL SECURITY INVOKER
       NO SQL
  BEGIN
    DECLARE wordCnt, idx, maxIdx INT DEFAULT 0;
    DECLARE currChar, prevChar BOOL DEFAULT 0;
    SET maxIdx=char_length(str);
    SET idx = 1;
    WHILE idx <= maxIdx DO
        SET currChar=SUBSTRING(str, idx, 1) RLIKE ''[[:alnum:]]'';
        IF NOT prevChar AND currChar THEN
            SET wordCnt=wordCnt+1;
        END IF;
        SET prevChar=currChar;
        SET idx=idx+1;
    END WHILE;
    RETURN wordCnt;
  END
$$
DELIMITER ;

Answer 3

Puede usar el UDF word_count() desde https://github.com/spachev/mysql_udf_bundle . Pagué la lógica de la respuesta aceptada con una diferencia de que mi código solo admite el juego de caracteres latin1. La lógica debería ser reelaborada para soportar otros conjuntos de caracteres. Además, ambas implementaciones siempre consideran que un carácter no alfanumérico es un delimitador, lo que puede no ser siempre deseable; por ejemplo, el "libro del maestro" se considera que tiene tres palabras en ambas implementaciones.

La versión UDF es, por supuesto, significativamente más rápida. Para una prueba rápida, probé ambos en un conjunto de datos del Proyecto Guttenberg que consta de 9751 registros que totalizan aproximadamente 3 GB. El UDF los hizo todos en 18 segundos, mientras que la función almacenada tardó 63 segundos en procesar solo 30 registros (lo que hace el UDF en 0.05 segundos). Así que el UDF es aproximadamente 1000 veces más rápido en este caso.

UDF superará cualquier otro método de velocidad que no implique modificar el código fuente de MySQL. Esto se debe a que tiene acceso a los bytes de cadena en la memoria y puede operar directamente en bytes sin que tengan que moverse. También se compila en código de máquina y se ejecuta directamente en la CPU.