Форум пользователей MySQL

explain select test.i from test, test t where test.i=5 and test.k=t.k order by t.k limit 5;

CREATE TABLE IF NOT EXISTS `topics` (
  `id` int(9) NOT NULL AUTO_INCREMENT,
  `id_country` int(3) NOT NULL,
  `id_rule` int(5) NOT NULL,
  `id_spec_group` int(3) NOT NULL,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `hash` binary(16) NOT NULL COMMENT 'pattern id_country+slug',
  `tree` varchar(100) NOT NULL COMMENT 'id.id.id_current',
  `tree_parent` int(7) NOT NULL,
  `tree_last` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'if last - 1 else 0',
  `template` varchar(100) NOT NULL DEFAULT 'category',
  `keyword_es_must` varchar(255) NOT NULL,
  `keyword_es_should` varchar(255) NOT NULL,
  `meta_title` varchar(255) NOT NULL,
  `meta_description` varchar(255) NOT NULL,
  `meta_canonical` varchar(255) NOT NULL,
  `meta_redirect` varchar(255) NOT NULL,
  `meta_robots_index` tinyint(1) NOT NULL DEFAULT '0',
  `meta_robots_archive` tinyint(1) NOT NULL DEFAULT '0',
  `meta_robots_follow` tinyint(1) NOT NULL DEFAULT '0',
  `is_category` tinyint(1) NOT NULL DEFAULT '1',
  `is_major` tinyint(1) NOT NULL DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `last_update` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `mongo_id` varchar(100) NOT NULL COMMENT 'remove',
  `statistics` varchar(255) NOT NULL,
  `facets` mediumblob NOT NULL,
  `keyword_es_mustshould` varchar(100) NOT NULL,
  `mongo_spec_id` varchar(100) NOT NULL COMMENT 'remove',
  `mongo_spec_group_id` varchar(100) NOT NULL COMMENT 'remove',
  `mongo_path` text NOT NULL COMMENT 'remove',
  PRIMARY KEY (`id`),
  UNIQUE KEY `hash` (`hash`),
  KEY `mongo_id` (`mongo_id`),
  KEY `name` (`name`),
  KEY `tree` (`tree`),
  KEY `last_update` (`last_update`),
  KEY `slug` (`slug`),
  KEY `is_category` (`is_category`),
  KEY `keyword_es_must` (`keyword_es_must`),
  KEY `meta_robots_index` (`meta_robots_index`),
  KEY `meta_robots_archive` (`meta_robots_archive`),
  KEY `meta_robots_follow` (`meta_robots_follow`),
  KEY `tree_parent` (`tree_parent`),
  KEY `is_active` (`is_active`),
  KEY `id_rule` (`id_rule`),
  KEY `tree_last` (`tree_last`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 PACK_KEYS=0 AUTO_INCREMENT=17696348 ;


CREATE TABLE IF NOT EXISTS `rules` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `description` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `min_items` int(3) NOT NULL DEFAULT '999',
  `min_news` int(3) NOT NULL DEFAULT '999',
  `min_reviews` int(3) NOT NULL DEFAULT '999',
  `min_blogs` int(3) DEFAULT '999',
  `mongo_id` varchar(100) COLLATE utf8_unicode_ci NOT NULL COMMENT 'remove',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci PACK_KEYS=0 AUTO_INCREMENT=102 ;

 

SELECT Lower(Hex(`topics`.`hash`)) AS `hash`,
       `topics`.`id`,
       `topics`.`id_country`,
       `topics`.`name`,
       `topics`.`slug`,
       `topics`.`keyword_es_must`,
       `topics`.`keyword_es_should`,
       `topics`.`tree`,
       `topics`.`keyword_es_mustshould`,
       `topics`.`meta_robots_index`,
       `rules`.`min_items`,
       `rules`.`min_news`,
       `rules`.`min_reviews`,
       `rules`.`min_blogs`
FROM   `topics`,
       `rules`
WHERE  `topics`.`id_rule` = `rules`.`id`
       AND `topics`.`meta_robots_index` = 0
       AND `topics`.`meta_robots_archive` = 0
       AND `topics`.`meta_robots_follow` = 0
       AND `topics`.`is_category` = 0
ORDER  BY `topics`.`last_update` ASC
LIMIT  0, 5000;