Re: Performance with select query that joins two tables one with 45MM and another with 12MM

[M Sundar]

Table Status:
contact_version InnoDB 10 Compact 12747103 332 4238344192 0 9211691008 0 12818618 2/18/2009 1:28 utf8_general_ci InnoDB free: 11310080 kB

owned_contact InnoDB 10 Compact 47587472 106 5048893440 0 9714728960 0 46573228 11/13/2008 15:43 utf8_general_ci InnoDB free: 11310080 kB


Tables:
CREATE TABLE `contact_version` (
`id` bigint(20) NOT NULL auto_increment,
`contact_id` bigint(20) NOT NULL default '0',
`contact_id_old` varchar(32) default NULL,
`version` int(11) NOT NULL default '0',
`locked` tinyint(1) NOT NULL default '0',
`creator_id` bigint(20) NOT NULL default '0',
`creator_id_old` varchar(32) default NULL,
`create_ts` timestamp NOT NULL default CURRENT_TIMESTAMP,
`update_ts` timestamp NOT NULL default '0000-00-00 00:00:00',
`rating` int(11) NOT NULL default '0',
`company_id` varchar(32) default NULL,
`company_id_new` bigint(20) unsigned NOT NULL,
`company_name` varchar(128) default NULL,
`title` varchar(128) NOT NULL,
`first_name` varchar(128) NOT NULL,
`last_name` varchar(128) NOT NULL,
`email` varchar(128) NOT NULL,
`email_domain` varchar(128) default NULL,
`phone` varchar(32) NOT NULL,
`address_id` bigint(20) default NULL,
`address` varchar(128) NOT NULL,
`city` varchar(128) NOT NULL,
`zip` varchar(32) default NULL,
`rank` int(11) default NULL,
`dept1` int(11) default NULL,
`dept2` int(11) default NULL,
`dept3` int(11) default NULL,
`change_ts` timestamp NOT NULL default '2004-04-24 07:00:00',
`origin_ts` timestamp NULL default NULL,
`dead` int(4) NOT NULL default '0',
`in_arbitration` tinyint(1) default '0',
`current_action_ts` timestamp NULL default NULL,
`metro_code` int(11) default '0',
`address_verified` tinyint(1) default '0',
`state` int(11) default NULL,
`country` int(11) NOT NULL,
`contact_source` tinyint(4) default '0',
`status_id` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `contactversion_contact_id_version_udx` USING BTREE (`contact_id`,`version`),
KEY `contactversion_changets_idx` USING BTREE (`change_ts`),
KEY `contactversion_createts_idx` USING BTREE (`create_ts`),
KEY `contactversion_updatets_idx` USING BTREE (`update_ts`),
KEY `contactversion_compname_idx` USING BTREE (`company_name`),
KEY `contactversion_origints_idx` USING BTREE (`origin_ts`),
KEY `contactversion_lastname_idx` USING BTREE (`last_name`),
KEY `contactversion_contact_id_idx` USING BTREE (`contact_id`),
KEY `contactversion_contact_id_old_idx` USING BTREE (`contact_id_old`),
KEY `contactversion_creator_id_old_idx` USING BTREE (`creator_id_old`),
KEY `contactversion_email_idx` USING BTREE (`email`),
KEY `contactversion_email_companyid_idx` (`company_id`,`email`),
KEY `contactversion_rank_idx` USING BTREE (`rank`),
KEY `contactversion_company_id_new_idx` (`company_id_new`),
KEY `contact_version_title_idx` USING BTREE (`title`),
KEY `contact_version_email_domain` (`email_domain`)
) ENGINE=InnoDB AUTO_INCREMENT=12818589 DEFAULT CHARSET=utf8

CREATE TABLE `owned_contact` (
`id` bigint(20) NOT NULL auto_increment,
`user_id` bigint(20) NOT NULL default '0',
`contact_id` bigint(20) NOT NULL default '0',
`owned_type` int(11) default NULL,
`owned_ts` timestamp NOT NULL default CURRENT_TIMESTAMP,
`category_id` bigint(20) default NULL,
`category_name` varchar(32) default NULL,
`corp_id` bigint(20) NOT NULL,
`update_ts` timestamp NOT NULL default '0000-00-00 00:00:00',
`last_earn_pts_ts` timestamp NULL default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `owned_contact_user_id_contact_id_idx` USING BTREE (`user_id`,`contact_id`),
UNIQUE KEY `owned_contact_corp_id_contact_id_uidx` USING BTREE (`corp_id`,`contact_id`),
KEY `ownedcontact_ownedts_idx` USING BTREE (`owned_ts`),
KEY `ownedcontact_contactid_idx` USING BTREE (`contact_id`),
KEY `ownedcontact_corpid_contactid_idx` USING BTREE (`corp_id`,`contact_id`)
) ENGINE=InnoDB AUTO_INCREMENT=46569551 DEFAULT CHARSET=utf8

Query:

select ownedconta0_.user_id as user1_568_0_, ownedconta0_.contact_id as
contact2_568_0_, syscontact1_.contact_id as contact1_566_1_, syscontact1_.version
as version566_1_, ownedconta0_.owned_type as owned3_568_0_, ownedconta0_.owned_ts
as owned4_568_0_, ownedconta0_.category_id as category5_568_0_,
ownedconta0_.category_name as category6_568_0_, ownedconta0_.corp_id as
corp7_568_0_, ownedconta0_.last_earn_pts_ts as last8_568_0_,
syscontact1_.company_id_new as company3_566_1_, syscontact1_.company_name
as company4_566_1_, syscontact1_.first_name as first5_566_1_,
syscontact1_.last_name as last6_566_1_, syscontact1_.email as email566_1_,
syscontact1_.email_domain as email8_566_1_, syscontact1_.title as title566_1_,
syscontact1_.phone as phone566_1_, syscontact1_.address as address566_1_,
syscontact1_.city as city566_1_, syscontact1_.zip as zip566_1_,
syscontact1_.rank as rank566_1_, syscontact1_.dept1 as dept15_566_1_,
syscontact1_.dept2 as dept16_566_1_, syscontact1_.dept3 as
dept17_566_1_, syscontact1_.create_ts as create18_566_1_,
syscontact1_.update_ts as update19_566_1_, syscontact1_.origin_ts as
origin20_566_1_, syscontact1_.change_ts as change21_566_1_,
syscontact1_.address_verified as address22_566_1_, syscontact1_.metro_code
as metro23_566_1_, syscontact1_.state as state566_1_, syscontact1_.country
as country566_1_, syscontact1_.locked as locked566_1_, syscontact1_.dead
as dead566_1_, syscontact1_.creator_id as creator28_566_1_,
syscontact1_.rating as rating566_1_, syscontact1_.contact_source as
contact30_566_1_, syscontact1_.in_arbitration as in31_566_1_,
syscontact1_.address_id as address32_566_1_ from
owned_contact ownedconta0_, contact_version syscontact1_ where
ownedconta0_.contact_id=syscontact1_.contact_id and
syscontact1_.rating=1 and ownedconta0_.user_id=10047243 and
ownedconta0_.owned_type<>10
order by syscontact1_.title limit 500;

If I remove the order by then the query becomes very faster like 2 seconds, also if I do the order by ownedconta0_.contacts_id then the query reponse is less than 5 seconds but same order by with syscontact1_.contact_id then query response time is very high.

Also attached the explain plan.
EXPLAIN EXTENDED select ownedconta0_.user_id as user1_568_0_, ownedconta0_.contact_id as contact2_568_0_, syscontact1_.contact_id as contact1_566_1_, syscontact1_.version as version566_1_, ownedconta0_.owned_type as owned3_568_0_, ownedconta0_.owned_ts as owned4_568_0_, ownedconta0_.category_id as category5_568_0_, ownedconta0_.category_name as category6_568_0_, ownedconta0_.corp_id as corp7_568_0_, ownedconta0_.last_earn_pts_ts as last8_568_0_, syscontact1_.company_id_new as company3_566_1_, syscontact1_.company_name as company4_566_1_, syscontact1_.first_name as first5_566_1_, syscontact1_.last_name as last6_566_1_, syscontact1_.email as email566_1_, syscontact1_.email_domain as email8_566_1_, syscontact1_.title as title566_1_, syscontact1_.phone as phone566_1_, syscontact1_.address as address566_1_, syscontact1_.city as city566_1_, syscontact1_.zip as zip566_1_, syscontact1_.rank as rank566_1_, syscontact1_.dept1 as dept15_566_1_, syscontact1_.dept2 as dept16_566_1_, syscontact1_.dept3 as dept17_566_1_, syscontact1_.create_ts as create18_566_1_, syscontact1_.update_ts as update19_566_1_, syscontact1_.origin_ts as origin20_566_1_, syscontact1_.change_ts as change21_566_1_, syscontact1_.address_verified as address22_566_1_, syscontact1_.metro_code as metro23_566_1_, syscontact1_.state as state566_1_, syscontact1_.country as country566_1_, syscontact1_.locked as locked566_1_, syscontact1_.dead as dead566_1_, syscontact1_.creator_id as creator28_566_1_, syscontact1_.rating as rating566_1_, syscontact1_.contact_source as contact30_566_1_, syscontact1_.in_arbitration as in31_566_1_, syscontact1_.address_id as address32_566_1_ from jigdata_0602.owned_contact ownedconta0_, jigdata_0602.contact_version syscontact1_ where ownedconta0_.contact_id=syscontact1_.contact_id and syscontact1_.rating=1 and ownedconta0_.corp_id=1000354 order by syscontact1_.title limit 501;

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"SIMPLE","ownedconta0_","ref","owned_contact_corp_id_contact_id_uidx,ownedcontact_contactid_idx,ownedcontact_corpid_contactid_idx","owned_contact_corp_id_contact_id_uidx","8","const",1278124,"Using temporary; Using filesort"
1,"SIMPLE","syscontact1_","ref","contactversion_contact_id_version_udx,contactversion_contact_id_idx","contactversion_contact_id_version_udx","8","jigdata_0602.ownedconta0_.contact_id",1,"Using where"

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"SIMPLE","ownedconta0_","ref","owned_contact_corp_id_contact_id_uidx,ownedcontact_contactid_idx,ownedcontact_corpid_contactid_idx","owned_contact_corp_id_contact_id_uidx","8","const",276674,"Using temporary; Using filesort"
1,"SIMPLE","syscontact1_","ref","contactversion_contact_id_version_udx,contactversion_contact_id_idx","contactversion_contact_id_version_udx","8","emurphy.ownedconta0_.contact_id",1,"Using where"