Description:
Not sure if this is already a known bug or not, but the sql_cache feature doesn't seem to be working when setting a user variable equal to the result set returned from a full text query.
e.g.
SET @i = (SELECT SQL_CACHE COUNT(*) FROM t1 WHERE MATCH (ft_column) AGAINST 'Google' );
The query above should run in about .3 seconds the first time (not cached), then then should run the second time in about .01 seconds (from the sql_cache (which doesn't seem to be working)).
Please execute the 'How to repeat:' script - then execute the two procedures below a few times, hopefully you will find what I mean..
CALL `test_db`.`test_proc_selectdata_without_user_variable`()
CALL `test_db`.`test_proc_selectdata_with_user_variable`()
P.S. I am quite new to MySQL and databases, so hopefully this all makes sense! Thanks ;)
How to repeat:
DROP DATABASE IF EXISTS test_db;
CREATE DATABASE IF NOT EXISTS test_db;
USE test_db;
CREATE TABLE `test_db`.`t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ft_column` varchar(1000) NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `ix_ft_column` (`ft_column`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
DELIMITER $$
DROP PROCEDURE IF EXISTS `test_db`.`test_proc_insertdata`$$
CREATE PROCEDURE `test_proc_insertdata`()
BEGIN
TRUNCATE t1;
SET @i = 0;
WHILE @i < 5000 DO
INSERT INTO t1(
ft_column
)
VALUES (
CONCAT(
MD5( CEIL(RAND() * 10 * 10 * 10 * 10 * 10) ), ' Google ',
MD5( CEIL(RAND() * 10 * 10 * 10 * 10 * 10) ), ' was ',
MD5( CEIL(RAND() * 10 * 10 * 10 * 10 * 10) ), ' created ',
MD5( CEIL(RAND() * 10 * 10 * 10 * 10 * 10) ), ' by ',
MD5( CEIL(RAND() * 10 * 10 * 10 * 10 * 10) ), ' Stanford ',
MD5( CEIL(RAND() * 10 * 10 * 10 * 10 * 10) ), ' students ',
MD5( CEIL(RAND() * 10 * 10 * 10 * 10 * 10) ), ' Larry ',
MD5( CEIL(RAND() * 10 * 10 * 10 * 10 * 10) ), ' Page ',
MD5( CEIL(RAND() * 10 * 10 * 10 * 10 * 10) ), ' and ',
MD5( CEIL(RAND() * 10 * 10 * 10 * 10 * 10) ), ' Sergey ',
MD5( CEIL(RAND() * 10 * 10 * 10 * 10 * 10) ), ' Brin '
)
);
SET @i = @i + 1;
END WHILE;
END$$
DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXISTS `test_db`.`test_proc_selectdata_without_user_variable`$$
CREATE PROCEDURE `test_proc_selectdata_without_user_variable`(
)
MainBlock:BEGIN
SELECT SQL_CACHE COUNT(*) FROM t1 WHERE
MATCH (t1.ft_column) AGAINST ('Google' IN BOOLEAN MODE);
END$$
DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXISTS `test_db`.`test_proc_selectdata_with_user_variable`$$
CREATE PROCEDURE `test_proc_selectdata_with_user_variable`(
)
MainBlock:BEGIN
SET @i = (
SELECT SQL_CACHE COUNT(*) FROM t1 WHERE
MATCH (t1.ft_column) AGAINST ('Google' IN BOOLEAN MODE)
);
SELECT @i;
END$$
DELIMITER ;
#truncate any existing data, then insert 5000 rows of test data...
CALL test_proc_insertdata();
#Try executing these a few times:
CALL `test_db`.`test_proc_selectdata_without_user_variable`()
CALL `test_db`.`test_proc_selectdata_with_user_variable`()
Suggested fix:
Not sure.
Description: Not sure if this is already a known bug or not, but the sql_cache feature doesn't seem to be working when setting a user variable equal to the result set returned from a full text query. e.g. SET @i = (SELECT SQL_CACHE COUNT(*) FROM t1 WHERE MATCH (ft_column) AGAINST 'Google' ); The query above should run in about .3 seconds the first time (not cached), then then should run the second time in about .01 seconds (from the sql_cache (which doesn't seem to be working)). Please execute the 'How to repeat:' script - then execute the two procedures below a few times, hopefully you will find what I mean.. CALL `test_db`.`test_proc_selectdata_without_user_variable`() CALL `test_db`.`test_proc_selectdata_with_user_variable`() P.S. I am quite new to MySQL and databases, so hopefully this all makes sense! Thanks ;) How to repeat: DROP DATABASE IF EXISTS test_db; CREATE DATABASE IF NOT EXISTS test_db; USE test_db; CREATE TABLE `test_db`.`t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `ft_column` varchar(1000) NOT NULL, PRIMARY KEY (`id`), FULLTEXT KEY `ix_ft_column` (`ft_column`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; DELIMITER $$ DROP PROCEDURE IF EXISTS `test_db`.`test_proc_insertdata`$$ CREATE PROCEDURE `test_proc_insertdata`() BEGIN TRUNCATE t1; SET @i = 0; WHILE @i < 5000 DO INSERT INTO t1( ft_column ) VALUES ( CONCAT( MD5( CEIL(RAND() * 10 * 10 * 10 * 10 * 10) ), ' Google ', MD5( CEIL(RAND() * 10 * 10 * 10 * 10 * 10) ), ' was ', MD5( CEIL(RAND() * 10 * 10 * 10 * 10 * 10) ), ' created ', MD5( CEIL(RAND() * 10 * 10 * 10 * 10 * 10) ), ' by ', MD5( CEIL(RAND() * 10 * 10 * 10 * 10 * 10) ), ' Stanford ', MD5( CEIL(RAND() * 10 * 10 * 10 * 10 * 10) ), ' students ', MD5( CEIL(RAND() * 10 * 10 * 10 * 10 * 10) ), ' Larry ', MD5( CEIL(RAND() * 10 * 10 * 10 * 10 * 10) ), ' Page ', MD5( CEIL(RAND() * 10 * 10 * 10 * 10 * 10) ), ' and ', MD5( CEIL(RAND() * 10 * 10 * 10 * 10 * 10) ), ' Sergey ', MD5( CEIL(RAND() * 10 * 10 * 10 * 10 * 10) ), ' Brin ' ) ); SET @i = @i + 1; END WHILE; END$$ DELIMITER ; DELIMITER $$ DROP PROCEDURE IF EXISTS `test_db`.`test_proc_selectdata_without_user_variable`$$ CREATE PROCEDURE `test_proc_selectdata_without_user_variable`( ) MainBlock:BEGIN SELECT SQL_CACHE COUNT(*) FROM t1 WHERE MATCH (t1.ft_column) AGAINST ('Google' IN BOOLEAN MODE); END$$ DELIMITER ; DELIMITER $$ DROP PROCEDURE IF EXISTS `test_db`.`test_proc_selectdata_with_user_variable`$$ CREATE PROCEDURE `test_proc_selectdata_with_user_variable`( ) MainBlock:BEGIN SET @i = ( SELECT SQL_CACHE COUNT(*) FROM t1 WHERE MATCH (t1.ft_column) AGAINST ('Google' IN BOOLEAN MODE) ); SELECT @i; END$$ DELIMITER ; #truncate any existing data, then insert 5000 rows of test data... CALL test_proc_insertdata(); #Try executing these a few times: CALL `test_db`.`test_proc_selectdata_without_user_variable`() CALL `test_db`.`test_proc_selectdata_with_user_variable`() Suggested fix: Not sure.