两个MySql表具有正确的索引，但JOIN在小表上需要9秒(Two MySql tables have correct indexes yet JOIN takes 9 seconds on small tables)
mysql
mysql Ver 14.14使用readline 5.1为redhat-linux-gnu（x86_64）分发5.1.73
我正在接管一个项目。 这是非常古老的原始程序员早已不复存在。 没有人知道为什么做出某些决定。
以下查询在9.5秒内运行（在我的Mac上）但是如果我删除最后一个JOIN则会下降到2.5秒。 最后一次加入有什么问题？
select `ttl`.`id` AS `id`, `ttl`.`name` AS `name`, `ttl`.`updated_at` AS `last_update_on`, `ttl`.`user_id` AS `list_creator`, `ttl`.`retailer_nomination_list` AS `nomination_list`, `ttl`.`created_at` AS `created_on`, count(distinct `tlb`.`title_id`) AS `title_count` from `haha_title_lists` `ttl` left join `haha_title_list_to_users` `tltu` on((`ttl`.`id` = `tltu`.`title_list_id`)) left join `users` `u` on((`tltu`.`user_id` = `u`.`id`)) left join `users` `u2` on((`tltu`.`user_id` = `u2`.`id`)) left join `haha_title_list_to_venues` `tlv` on((`ttl`.`id` = `tlv`.`title_list`)) left join `haha_venue_properties` `tvp` on((`tlv`.`venue_id` = `tvp`.`id`)) join `haha_title_list_to_books` `tlb` on((`ttl`.`id` = `tlb`.`title_list_id`)) join `wawa_title` `ot` on((`tlb`.`title_id` = `ot`.`title_id`)) group by `ttl`.`id`;表格：
CREATE TABLE `haha_title_list_to_books` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `title_id` int(11) NOT NULL, `title_list_id` int(11) NOT NULL, `sdk` varchar(15) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `created_at` datetime NOT NULL, `promo_start_date` date DEFAULT NULL, `promo_end_date` date DEFAULT NULL, `promo_price` float DEFAULT NULL, `confirmations` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `nominations` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `title_note` text COLLATE utf8_unicode_ci, `executed` int(11) DEFAULT NULL, `event_created` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_promo_start_date` (`promo_start_date`), KEY `idx_promo_end_date` (`promo_end_date`), KEY `idx_title_list_to_books_title_id` (`title_id`), KEY `idx_title_list_to_books_title_list_id` (`title_list_id`) ) ENGINE=MyISAM AUTO_INCREMENT=21847 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci和：
CREATE TABLE `wawa_title` ( `title_id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(200) DEFAULT NULL, `title_alpha` varchar(25) NOT NULL, `display_title` varchar(200) NOT NULL, `subtitle` text NOT NULL, `sdk10` varchar(13) DEFAULT '', `sdk13` varchar(15) DEFAULT NULL, `primary_sdk13` varchar(15) DEFAULT NULL, `asin` varchar(10) DEFAULT NULL, `pub_season` varchar(15) NOT NULL, `pub_year` varchar(15) NOT NULL, `bisac1` varchar(15) NOT NULL, `bisac2` varchar(15) NOT NULL, `bisac3` varchar(15) NOT NULL, `barcode` varchar(30) DEFAULT NULL, `dewey_decimal` varchar(15) NOT NULL, `lib_of_congress` varchar(15) NOT NULL, `spanish_language` tinyint(4) NOT NULL, `target_audience` tinyint(3) unsigned DEFAULT NULL, `language` varchar(20) DEFAULT NULL, `edition` varchar(45) DEFAULT NULL, `pages` int(11) DEFAULT NULL, `number_in_series` int(11) DEFAULT NULL, `trimsize` varchar(10) DEFAULT NULL, `filesize` varchar(10) DEFAULT NULL, `duration_hours` int(11) DEFAULT NULL, `duration_minutes` int(11) DEFAULT NULL, `discs` int(11) DEFAULT NULL, `download` date DEFAULT NULL, `size_unit` varchar(15) NOT NULL DEFAULT '', `digitization_date` date NOT NULL, `us_on_sale_date` date NOT NULL, `aus_on_sale_date` date NOT NULL, `can_on_sale_date` date NOT NULL, `uk_on_sale_date` date NOT NULL, `us_list_price` varchar(10) NOT NULL, `aus_list_price` varchar(10) NOT NULL, `can_list_price` varchar(10) NOT NULL, `uk_list_price` varchar(10) NOT NULL, `isPrimary` varchar(1) DEFAULT NULL, `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `modifier` int(11) NOT NULL, `activated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `active` varchar(3) NOT NULL DEFAULT 'N', `flagged_string` text, `created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `assets_id` varchar(20) DEFAULT NULL, `book_details` text, `book_keynote` text, `exclude_goodreads` char(1) NOT NULL DEFAULT 'N', `series_description` text, `review_quote1` text, `territory_id` int(11) DEFAULT '27', `featured_newsletter_id` tinyint(3) unsigned DEFAULT '0', `retailer_discovery_check` datetime DEFAULT NULL, `suppress_retailer_approval` tinyint(1) DEFAULT '0', `suppress_retailer_approval_reason` varchar(255) DEFAULT NULL, `ebb_description` text CHARACTER SET utf8 COLLATE utf8_unicode_ci, `slug` varchar(150) DEFAULT NULL, `legacy_slug` varchar(150) DEFAULT NULL, `us_agency_price` varchar(10) DEFAULT NULL, `firebrand_title_id` int(11) DEFAULT NULL, `ebb_label` varchar(200) DEFAULT NULL, `ebb_end_sale_date` date DEFAULT NULL, `ebb_downprice` decimal(10,2) DEFAULT NULL, `book_club` varchar(1) DEFAULT NULL, `best_seller` varchar(1) DEFAULT NULL, `award_winner` varchar(1) DEFAULT NULL, `discovery` char(1) NOT NULL DEFAULT 'Y', `narrator_id` int(11) DEFAULT NULL, `suppress_series_data` varchar(255) DEFAULT NULL, PRIMARY KEY (`title_id`), KEY `active_index` (`active`), KEY `fk_title_series_id_idx` (`series_id`), KEY `series_id` (`series_id`), KEY `idx_title_sdk13` (`sdk13`), KEY `idx_title_active_isprimary` (`active`,`isPrimary`), KEY `bisac1` (`bisac1`), KEY `bisac2` (`bisac2`), KEY `bisac3` (`bisac3`), KEY `idx_primary_sdk13` (`primary_sdk13`), KEY `idx_territory_id` (`territory_id`), CONSTRAINT `fk_title_series_id` FOREIGN KEY (`series_id`) REFERENCES `wawa_series` (`series_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=19700 DEFAULT CHARSET=utf8 |如果我删除此行：
join `wawa_title` `ot` on((`tlb`.`title_id` = `ot`.`title_id`))查询速度从9.5秒降至2.5秒。 不是很好，但是有了很大的改进。
然而，两个表都有table_id上的索引，那么为什么这条线会成为一个问题呢？
我注意到一个表是InnoDB，另一个表是MyISAM。 这会产生影响吗？
mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
I am taking over a project. It is very old and the original programmer is long gone. No one has any idea why certain decisions were made.
The following query runs (on my Mac) in 9.5 seconds but if I remove the last JOIN then it drops to 2.5 seconds. What is wrong with that last JOIN?
select `ttl`.`id` AS `id`, `ttl`.`name` AS `name`, `ttl`.`updated_at` AS `last_update_on`, `ttl`.`user_id` AS `list_creator`, `ttl`.`retailer_nomination_list` AS `nomination_list`, `ttl`.`created_at` AS `created_on`, count(distinct `tlb`.`title_id`) AS `title_count` from `haha_title_lists` `ttl` left join `haha_title_list_to_users` `tltu` on((`ttl`.`id` = `tltu`.`title_list_id`)) left join `users` `u` on((`tltu`.`user_id` = `u`.`id`)) left join `users` `u2` on((`tltu`.`user_id` = `u2`.`id`)) left join `haha_title_list_to_venues` `tlv` on((`ttl`.`id` = `tlv`.`title_list`)) left join `haha_venue_properties` `tvp` on((`tlv`.`venue_id` = `tvp`.`id`)) join `haha_title_list_to_books` `tlb` on((`ttl`.`id` = `tlb`.`title_list_id`)) join `wawa_title` `ot` on((`tlb`.`title_id` = `ot`.`title_id`)) group by `ttl`.`id`;The tables:
CREATE TABLE `haha_title_list_to_books` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `title_id` int(11) NOT NULL, `title_list_id` int(11) NOT NULL, `sdk` varchar(15) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `created_at` datetime NOT NULL, `promo_start_date` date DEFAULT NULL, `promo_end_date` date DEFAULT NULL, `promo_price` float DEFAULT NULL, `confirmations` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `nominations` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `title_note` text COLLATE utf8_unicode_ci, `executed` int(11) DEFAULT NULL, `event_created` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_promo_start_date` (`promo_start_date`), KEY `idx_promo_end_date` (`promo_end_date`), KEY `idx_title_list_to_books_title_id` (`title_id`), KEY `idx_title_list_to_books_title_list_id` (`title_list_id`) ) ENGINE=MyISAM AUTO_INCREMENT=21847 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ciand:
CREATE TABLE `wawa_title` ( `title_id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(200) DEFAULT NULL, `title_alpha` varchar(25) NOT NULL, `display_title` varchar(200) NOT NULL, `subtitle` text NOT NULL, `sdk10` varchar(13) DEFAULT '', `sdk13` varchar(15) DEFAULT NULL, `primary_sdk13` varchar(15) DEFAULT NULL, `asin` varchar(10) DEFAULT NULL, `pub_season` varchar(15) NOT NULL, `pub_year` varchar(15) NOT NULL, `bisac1` varchar(15) NOT NULL, `bisac2` varchar(15) NOT NULL, `bisac3` varchar(15) NOT NULL, `barcode` varchar(30) DEFAULT NULL, `dewey_decimal` varchar(15) NOT NULL, `lib_of_congress` varchar(15) NOT NULL, `spanish_language` tinyint(4) NOT NULL, `target_audience` tinyint(3) unsigned DEFAULT NULL, `language` varchar(20) DEFAULT NULL, `edition` varchar(45) DEFAULT NULL, `pages` int(11) DEFAULT NULL, `number_in_series` int(11) DEFAULT NULL, `trimsize` varchar(10) DEFAULT NULL, `filesize` varchar(10) DEFAULT NULL, `duration_hours` int(11) DEFAULT NULL, `duration_minutes` int(11) DEFAULT NULL, `discs` int(11) DEFAULT NULL, `download` date DEFAULT NULL, `size_unit` varchar(15) NOT NULL DEFAULT '', `digitization_date` date NOT NULL, `us_on_sale_date` date NOT NULL, `aus_on_sale_date` date NOT NULL, `can_on_sale_date` date NOT NULL, `uk_on_sale_date` date NOT NULL, `us_list_price` varchar(10) NOT NULL, `aus_list_price` varchar(10) NOT NULL, `can_list_price` varchar(10) NOT NULL, `uk_list_price` varchar(10) NOT NULL, `isPrimary` varchar(1) DEFAULT NULL, `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `modifier` int(11) NOT NULL, `activated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `active` varchar(3) NOT NULL DEFAULT 'N', `flagged_string` text, `created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `assets_id` varchar(20) DEFAULT NULL, `book_details` text, `book_keynote` text, `exclude_goodreads` char(1) NOT NULL DEFAULT 'N', `series_description` text, `review_quote1` text, `territory_id` int(11) DEFAULT '27', `featured_newsletter_id` tinyint(3) unsigned DEFAULT '0', `retailer_discovery_check` datetime DEFAULT NULL, `suppress_retailer_approval` tinyint(1) DEFAULT '0', `suppress_retailer_approval_reason` varchar(255) DEFAULT NULL, `ebb_description` text CHARACTER SET utf8 COLLATE utf8_unicode_ci, `slug` varchar(150) DEFAULT NULL, `legacy_slug` varchar(150) DEFAULT NULL, `us_agency_price` varchar(10) DEFAULT NULL, `firebrand_title_id` int(11) DEFAULT NULL, `ebb_label` varchar(200) DEFAULT NULL, `ebb_end_sale_date` date DEFAULT NULL, `ebb_downprice` decimal(10,2) DEFAULT NULL, `book_club` varchar(1) DEFAULT NULL, `best_seller` varchar(1) DEFAULT NULL, `award_winner` varchar(1) DEFAULT NULL, `discovery` char(1) NOT NULL DEFAULT 'Y', `narrator_id` int(11) DEFAULT NULL, `suppress_series_data` varchar(255) DEFAULT NULL, PRIMARY KEY (`title_id`), KEY `active_index` (`active`), KEY `fk_title_series_id_idx` (`series_id`), KEY `series_id` (`series_id`), KEY `idx_title_sdk13` (`sdk13`), KEY `idx_title_active_isprimary` (`active`,`isPrimary`), KEY `bisac1` (`bisac1`), KEY `bisac2` (`bisac2`), KEY `bisac3` (`bisac3`), KEY `idx_primary_sdk13` (`primary_sdk13`), KEY `idx_territory_id` (`territory_id`), CONSTRAINT `fk_title_series_id` FOREIGN KEY (`series_id`) REFERENCES `wawa_series` (`series_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=19700 DEFAULT CHARSET=utf8 |If I remove this line:
join `wawa_title` `ot` on((`tlb`.`title_id` = `ot`.`title_id`))The query speed drops from 9.5 seconds to 2.5 seconds. Not great, but a huge improvement.
And yet, both tables have indexes on table_id, so why would that line be a problem?
I notice that one table is InnoDB and the other is MyISAM. Would that have an effect?
最满意答案
不要JOIN不使用的表。
JOIN经常“爆炸”行数，然后就像你有行数的卷轴一样。 要查看此内容，请将所有JOIN保留在那里，但删除GROUP BY 。 查看您获得的行数。
为了避免爆炸的一部分，改变
count(distinct `tlb`.`title_id`) AS `title_count`至
( SELECT count(distinct `title_id`) FROM `haha_title_list_to_books` WHERE `ttl`.`id` = `title_list_id` ) AS `title_count`并删除当前JOIN到tlb。
混合MyISAM和InnoDB不应该对此SELECT产生任何直接影响。 但是，您应该考虑将所有表移动到InnoDB。
Do not JOIN to tables that you don't use.
A JOIN often "explodes" the number of rows, then a GROUP BY like you have reels in the number of rows. To see this, leave all the JOINs there, but remove the GROUP BY. See how many rows you get.
To avoid part of that explosion, change
count(distinct `tlb`.`title_id`) AS `title_count`to
( SELECT count(distinct `title_id`) FROM `haha_title_list_to_books` WHERE `ttl`.`id` = `title_list_id` ) AS `title_count`and remove the current JOIN to tlb.
Mixing MyISAM and InnoDB should not have any direct impact on this SELECT. However, you should consider moving all of your tables to InnoDB.
更多推荐
发布评论