6GJHkJv8

1-- Given table NEWS with textual column FULL_TEXT
2DROP PROCEDURE IF EXISTS populate_counter;
3DELIMITER //
4CREATE PROCEDURE populate_counter ()
5BEGIN
6    DROP TABLE IF EXISTS counter;
7    CREATE TABLE counter (
8    	id int unsigned NOT NULL AUTO_INCREMENT,
9    	word VARCHAR(8000),
10    	usageCount int(20),
11    	primary key (id)
12	);
13	
14    SET @wordCt  = 0;
15    SET @tokenCt = 1;
16
17    contentLoop: LOOP
18        SET @stmt = 'INSERT INTO counter (word) SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(`full_text`, " ", ?),
19                                LENGTH(SUBSTRING_INDEX(`full_text`, " ", ? -1)) + 1),
20                                " ", "") word
21                     FROM news
22                     WHERE LENGTH(SUBSTRING_INDEX(`full_text`, " ", ?)) != LENGTH(`full_text`)';
23        PREPARE cmd FROM @stmt;
24        EXECUTE cmd USING @tokenCt, @tokenCt, @tokenCt;
25        SELECT ROW_COUNT() INTO @wordCt;
26        DEALLOCATE PREPARE cmd;
27        IF (@wordCt = 0) THEN
28            LEAVE contentLoop;
29        ELSE
30            SET @tokenCt = @tokenCt + 1;
31        END IF;
32    END LOOP;
33
34    SELECT word, count(*) usageCount FROM counter GROUP BY word ORDER BY usageCount DESC;
35END //
36DELIMITER ;
37
38call populate_counter();