当前位置: 动力学知识库 > 问答 > 编程问答 >

php - Query optimization on millions of records

问题描述:

I try to optimize my query , for have a better time of response with the following query

SELECT * FROM `videos`

WHERE MATCH (name,tags)

AGAINST ('toto' IN BOOLEAN MODE)

ORDER BY `id` DESC, `id` DESC LIMIT 8

Database structure

Navicat MySQL Data Transfer

Source Server : host

Source Server Version : 50550

Source Host : 137.74.231.89:3306

Source Database : megaboss

Target Server Type : MYSQL

Target Server Version : 50550

File Encoding : 65001

Date: 2016-09-28 20:43:25

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for videos

-- ----------------------------

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;

With a records count

Loading Time: Base Classes 0.0069

Controller Execution Time ( Engine / Search ) 1.3354

Total Execution Time 1.3430

Without a records count

Loading Time: Base Classes 0.0063

Controller Execution Time ( Engine / Search ) 0.0147

Total Execution Time 0.0216

It's very slow when i count the number of results ( for my pagination system )

$query->get('videos')->num_rows();

Any solutions ?

分享给朋友:
您可能感兴趣的文章:
随机阅读: