搜索查询优化Mysql [英] search query optimization Mysql
问题描述
我尝试对一个表中包含超过120万条记录的以下查询进行优化。
#1 - 执行时间:0.6605,用于取得项目
SELECT * FROM`videos` WHERE MATCH(name,tags)AGAINST('mytag'IN BOOLEAN MODE)LIMIT 20
*#2 - 执行时间:0.5186,用于分页系统*
SELECT COUNT(*)AS`numrows` FROM`videos` WHERE MATCH(名称,标签)AGAINST('mytag'IN BOOLEAN MODE)
我的表格结构
DROP TABLE IF EXISTS`videos`;
CREATE TABLE`videos`(
`id` int(11)NOT NULL AUTO_INCREMENT,
`name` varchar(255)NOT NULL,
`name_url` varchar DEFAULT NULL,
`categories` varchar(255)DEFAULT NULL,
`embed` tinytext,
`description` text,
` b $ b`hd` smallint(6)DEFAULT'0',
`views` int(11)NOT NULL DEFAULT'0',
`likes` int(11)DEFAULT'0' b $ b`dislikes` int(11)DEFAULT'0',
`cover` varchar(255)DEFAULT NULL,
PRIMARY KEY(`id`,`name`),
FULLTEXT KEY`category`(`categories`),
FULLTEXT KEY`name`(`name`),
FULLTEXT KEY`tags`(`tags`)
)ENGINE = MyISAM AUTO_INCREMENT = 1273355 DEFAULT CHARSET = latin1;
任何解决方案对每个查询都有快速回复? / strong>
解决方案
p>索引应该加载到RAM中,但MyISAM有一个有限大小的缓冲区来存储索引。您可能需要使用 key_buffer_size
配置选项增加MyISAM索引缓冲区的大小。
我不知道你的服务器规格是什么。它必须是一个相当弱的服务器,因为我已经做了测试使用MySQL全文索引740万行数据,并且查询执行在16毫秒。这是在Macbook Pro,真实的服务器应该更快。
查看我的演示文稿关于全文搜索MySQL在这里: http://www.slideshare.net/billkarwin/practical-full-text-search-with- my-sql
I try to optimize the following queries on a table contains more than 1.2 millions of records .
#1 - Execution time : 0.6605 , used for get the items
SELECT * FROM `videos` WHERE MATCH (name,tags) AGAINST ('mytag' IN BOOLEAN MODE) LIMIT 20
*#2 - Execution time : 0.5186 , used for the pagination system *
SELECT COUNT(*) AS `numrows` FROM `videos` WHERE MATCH (name,tags) AGAINST ('mytag' IN BOOLEAN MODE)
Stucture of my table
DROP TABLE IF EXISTS `videos`;
CREATE TABLE `videos` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`name_url` varchar(255) DEFAULT NULL,
`categories` varchar(255) DEFAULT NULL,
`embed` tinytext,
`description` text,
`tags` varchar(255) DEFAULT NULL,
`hd` smallint(6) DEFAULT '0',
`views` int(11) NOT NULL DEFAULT '0',
`likes` int(11) DEFAULT '0',
`dislikes` int(11) DEFAULT '0',
`cover` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`,`name`),
FULLTEXT KEY `categories` (`categories`),
FULLTEXT KEY `name` (`name`),
FULLTEXT KEY `tags` (`tags`)
) ENGINE=MyISAM AUTO_INCREMENT=1273355 DEFAULT CHARSET=latin1;
Any solution for have fast responses for each queries ?
Best regards
解决方案 Indexes should be loaded into RAM, but MyISAM has a finite-sized buffer to store indexes. You may need to increase the size of your MyISAM index buffer with the key_buffer_size
configuration option.
I have no idea what your server specs are. It must be a pretty underpowered server, because I've done tests with a MySQL fulltext index on 7.4 million rows of data, and had queries execute in 16 milliseconds. This was on a Macbook Pro, and a real server should be even faster.
See my presentation about fulltext search in MySQL here: http://www.slideshare.net/billkarwin/practical-full-text-search-with-my-sql
这篇关于搜索查询优化Mysql的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!