mysql-索引使用

mysql 如何给SQL添加索引
1.添加PRIMARY KEY(主键索引)

alter table table_name add primary key(column);

2.添加UNIQUE(唯一索引)

alter table table_name add unique(column);

3.添加普通索引

alter table table_name add index index_name(column);

4.添加全文索引

alter table table_name add fulltext(column);

5.添加多列索引

alter table table_name add index index_name(column1,column2,column3);

实践

只给需要的字段添加，因为是用 lasaleagent表中的 idno查，所以只需要给其加就可以

SELECT
	( select agentcom from lacom where agentcom in (SELECT agentcom FROM lasaleagent WHERE 1 = 1 AND agentstate = 'Y' AND BranchType = '6' AND IDNoType = '5' AND IDNo = '123456789' union all SELECT agentcom FROM lasaleagenttemp WHERE 1 = 1 AND agentstate != '05' AND BranchType = '6' AND IDNoType = '5' AND IDNo = '123456789') limit 1 ) 
FROM
DUAL 
WHERE
	1 = 1 
	AND (
		EXISTS ( SELECT 'Y' FROM lasaleagent WHERE 1 = 1 AND agentstate = 'Y' AND BranchType = '6' AND IDNoType = '5' AND IDNo = '123456789' ) 
		OR EXISTS ( SELECT 'Y' FROM lasaleagenttemp WHERE 1 = 1 AND agentstate != '05' AND BranchType = '6' AND IDNoType = '5' AND IDNo = '123456789' ) 
	)


alter table `lasaleagent` add index index_lasaleagent_idno(`idno`);
alter table `lasaleagenttemp` add index index_lasaleagenttemp_idno(`idno`);

子查询只能返回一列，可以通过concat函数

SELECT ( select concat(agentcom,'-',name) from lacom where agentcom in (SELECT agentcom FROM lasaleagent WHERE 1 = 1 AND agentstate = 'Y'  and BranchType='6'  and IDNoType='5'  and IDNo='123456789' union all SELECT agentcom FROM lasaleagenttemp WHERE 1 = 1 AND agentstate != '05'  and BranchType='6'  and IDNoType='5'  and IDNo='123456789' ) limit 1 )  FROM DUAL  WHERE 1 = 1  AND ( EXISTS (  SELECT 'Y'  FROM lasaleagent  WHERE 1 = 1  AND agentstate = 'Y'  and BranchType='6'  and IDNoType='5'  and IDNo='123456789'  ) OR EXISTS (  SELECT 'Y'  FROM lasaleagenttemp  WHERE 1 = 1  AND agentstate != '05'  and BranchType='6'  and IDNoType='5'  and IDNo='123456789'  )  )