-- 【SQL脚本如下:】
SELECT c.id,c.status,m.guid FROM tb_contact c
LEFT OUTER JOIN tb_client_mapping m
ON c.id = m.guid AND m.principal = 42959 AND m.sync_source = 'card'
WHERE c.userid = '28727' AND
( ( c.status <> 'D' AND c.status <> 'R' AND m.guid IS NULL)
OR
( c.status = 'D' AND c.status = 'R' AND m.guid IS NOT NULL)
);--【执行结果及执行时间:】
Empty set (9.53 sec)
-- 【执行计划:】
id type table type possible_keys key key_len ref rows
1 SIMPLE c ref idx_uk_contact_uid_update_status idx_uk_contact_uid_update_status 8 const 11704
1 SIMPLE m ref idx_princ_sync_luid_guid,fk_source_cm idx_principal_sync_luid_guid 202 const,const 1--【所使用表的数据量】
tb_contact : 5037259
tb_client_mapping : 5353325请大侠们给优化一下! 感激~~~~~
SELECT c.id,c.status,m.guid FROM tb_contact c
LEFT OUTER JOIN tb_client_mapping m
ON c.id = m.guid AND m.principal = 42959 AND m.sync_source = 'card'
WHERE c.userid = '28727' AND
( ( c.status <> 'D' AND c.status <> 'R' AND m.guid IS NULL)
OR
( c.status = 'D' AND c.status = 'R' AND m.guid IS NOT NULL)
);--【执行结果及执行时间:】
Empty set (9.53 sec)
-- 【执行计划:】
id type table type possible_keys key key_len ref rows
1 SIMPLE c ref idx_uk_contact_uid_update_status idx_uk_contact_uid_update_status 8 const 11704
1 SIMPLE m ref idx_princ_sync_luid_guid,fk_source_cm idx_principal_sync_luid_guid 202 const,const 1--【所使用表的数据量】
tb_contact : 5037259
tb_client_mapping : 5353325请大侠们给优化一下! 感激~~~~~
解决方案 »
- MySQL 程序Release问题
- 请教高手!Mysql中的难题(用百度、google未搜到解决办法)
- 在mysq中如何查找整个数据库的索引情况
- 求助一条mysql语句
- win7家庭版能装mysql吗?
- 请问各位,在SQL2000与MySQL怎样保持数据的同步呢?有这功能吗?急!急!急!在线等待……
- mySQL安装时配置错误,快绝望了,求各位大神指导
- mysql存储过程
- CENTOS 搭建XAMMP 写了一个.C来调用MYSQL,为什么不能连接数据库?
- “System.InvalidOperationException”类型的未经处理的异常出现在 MySql.Data.dll 中。
- mysql怪异问题
- 求教用批处理文件创建一个数据库和一个表
1 SIMPLE m ref idx_princ_sync_luid_guid,fk_source_cm idx_principal_sync_luid_guid 202 const,const 1这个索引长度可能有点儿长 因为是组合索引
show create table tb_contact;
show create table tb_client_mapping;
select count(*) from tb_client_mapping where principal = 42959 and sync_source = 'card'
select count(*) from tb_contact where userid = '28727'
show create table tb_client_mapping;CREATE TABLE `tb_contact` (
`cid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`id` bigint(20) unsigned NOT NULL DEFAULT '0',
`userid` bigint(20) unsigned NOT NULL DEFAULT '0',
`last_update` bigint(20) unsigned NOT NULL DEFAULT '0',
`create_date` bigint(20) unsigned NOT NULL DEFAULT '0',
`status` char(1) DEFAULT NULL,
`photo_type` smallint(5) unsigned NOT NULL DEFAULT '0',
`importance` smallint(5) unsigned NOT NULL DEFAULT '0',
`sensitivity` smallint(5) unsigned NOT NULL DEFAULT '0',
`subject` varchar(255) DEFAULT NULL,
`folder` varchar(255) DEFAULT NULL,
`anniversary` varchar(16) DEFAULT NULL,
`first_name` varchar(255) DEFAULT NULL,
`middle_name` varchar(255) DEFAULT NULL,
`last_name` varchar(255) DEFAULT NULL,
`display_name` varchar(255) DEFAULT NULL,
`birthday` varchar(16) DEFAULT NULL,
`body` text,
`categories` varchar(255) DEFAULT NULL,
`children` varchar(255) DEFAULT NULL,
`hobbies` varchar(255) DEFAULT NULL,
`initials` varchar(16) DEFAULT NULL,
`languages` varchar(255) DEFAULT NULL,
`nickname` varchar(255) DEFAULT NULL,
`spouse` varchar(128) DEFAULT NULL,
`suffix` varchar(32) DEFAULT NULL,
`title` varchar(32) DEFAULT NULL,
`gender` char(1) DEFAULT NULL,
`assistant` varchar(255) DEFAULT NULL,
`company` varchar(255) DEFAULT NULL,
`department` varchar(255) DEFAULT NULL,
`job_title` varchar(255) DEFAULT NULL,
`manager` varchar(255) DEFAULT NULL,
`mileage` varchar(255) DEFAULT NULL,
`office_location` varchar(255) DEFAULT NULL,
`profession` varchar(255) DEFAULT NULL,
`companies` varchar(255) DEFAULT NULL,
`starred` varchar(1) DEFAULT NULL,
`accountName` varchar(64) DEFAULT NULL,
`accountType` varchar(64) DEFAULT NULL,
`smsSourceUrl` varchar(128) DEFAULT NULL,
`callSourceUrl` varchar(128) DEFAULT NULL,
`short_name` varchar(255) DEFAULT NULL,
`isshare` smallint(5) unsigned NOT NULL DEFAULT '0',
`br_interval` smallint(5) unsigned NOT NULL DEFAULT '0',
`ar_interval` smallint(5) unsigned NOT NULL DEFAULT '0',
`isremind` varchar(32) DEFAULT NULL,
PRIMARY KEY (`cid`),
UNIQUE KEY `idx_uk_contact` (`id`),
KEY `idx_uk_contact_uid_update_status` (`userid`,`last_update`,`status`),
KEY `idx_contact_firstname` (`first_name`),
KEY `idx_contact_shorttname` (`short_name`)
) ENGINE=InnoDB AUTO_INCREMENT=10228512 DEFAULT CHARSET=utf8
CREATE TABLE `tb_client_mapping` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`principal` bigint(20) unsigned NOT NULL DEFAULT '0',
`sync_source` varchar(64) NOT NULL,
`luid` varchar(128) NOT NULL,
`guid` varchar(128) NOT NULL,
`last_anchor` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_princ_sync_luid_guid` (`principal`,`sync_source`,`luid`,`guid`),
KEY `fk_source_cm` (`sync_source`),
CONSTRAINT `fk_card_principal_cm` FOREIGN KEY (`principal`) REFERENCES `fnbl_principal` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_card_crecord_cm` FOREIGN KEY (`sync_source`) REFERENCES `fnbl_sync_source` (`uri`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=12919037 DEFAULT CHARSET=utf8
show index from tb_contact;
show index from tb_client_mapping优化的主要麻烦估计在
( c.status <> 'D' AND c.status <> 'R' AND m.guid IS NULL)
OR
( c.status = 'D' AND c.status = 'R' AND m.guid IS NOT NULL)
| count(*) |
+----------+
| 5627 |
+----------+
select count(*) from tb_contact where userid = '28727'+----------+
| count(*) |
+----------+
| 5627 |
+----------+