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_ci
and:
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?
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.