mysql - 优化查询(2 个简单的左连接)

Question

SELECT fcat.id,fcat.title,fcat.description, 
count(DISTINCT ftopic.id) as number_topics, 
count(DISTINCT fpost.id) as number_posts FROM fcat 
LEFT JOIN ftopic ON fcat.id=ftopic.cat_id 
LEFT JOIN fpost ON ftopic.id=fpost.topic_id 
GROUP BY fcat.id
ORDER BY fcat.ord
LIMIT 100;

ftopic_cat_id、fpost.topic_id、fcat.ord 上的索引

解释:

id      select_type     table   type    possible_keys       key         key_len     ref             rows    Extra
1       SIMPLE          fcat    ALL     PRIMARY             NULL        NULL        NULL            11      Using temporary; Using filesort
1       SIMPLE          ftopic  ref     PRIMARY,cat_id_2    cat_id_2    4           bloki.fcat.id   72   
1       SIMPLE          fpost   ref     topic_id_2          topic_id_2  4           bloki.ftopic.id 245 

fcat - 11 行，ftopic - 1106 行，fpost - 363000 行

查询需要 4.2 秒

表格:

CREATE TABLE IF NOT EXISTS `fcat` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(250) collate utf8_unicode_ci NOT NULL,
  `description` varchar(250) collate utf8_unicode_ci NOT NULL,
  `created` datetime NOT NULL,
  `visible` tinyint(4) NOT NULL default '1',
  `ord` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `ord` (`ord`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=12 ;

CREATE TABLE IF NOT EXISTS `ftopic` (
  `id` int(11) NOT NULL auto_increment,
  `cat_id` int(11) NOT NULL,
  `title` varchar(100) collate utf8_unicode_ci NOT NULL,
  `created` datetime NOT NULL,
  `updated` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `lastname` varchar(200) collate utf8_unicode_ci NOT NULL,
  `visible` tinyint(4) NOT NULL default '1',
  `closed` tinyint(4) NOT NULL default '0',
  `views` int(11) NOT NULL default '1',
  PRIMARY KEY  (`id`),
  KEY `cat_id_2` (`cat_id`,`updated`,`visible`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1116 ;

CREATE TABLE IF NOT EXISTS `fpost` (
  `id` int(11) NOT NULL auto_increment,
  `topic_id` int(11) NOT NULL,
  `pet_id` int(11) NOT NULL,
  `content` text collate utf8_unicode_ci NOT NULL,
  `imageName` varchar(300) collate utf8_unicode_ci NOT NULL,
  `created` datetime NOT NULL,
  `reply_id` int(11) NOT NULL,
  `visible` tinyint(4) NOT NULL default '1',
  `md5` varchar(100) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `md5` (`md5`),
  KEY `topic_id_2` (`topic_id`,`created`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=390971 ;

谢谢，小村庄

Answer 1

您需要使用 fcat.id, fcat.ord 创建一个 key