mysql查询命令优化实例

1.0版：查询时间15秒
select * from intd
left join
gengxin
on intd.id = gengxin.intd_id
where (gengxin.time in (select max(time) from gengxin group by intd_id) or gengxin.time is null) and (intd.xm1 like '小明' or intd.xm7 like '小明') order by gengxin.time desc



2.0版：查询时间6秒
select * from (
select * from intd
left join gengxin on intd.id = gengxin.intd_id
where gengxin.time in (select max(time) from gengxin group by intd_id) and intd.xm1 like '小明'
union all
select * from intd
left join gengxin on intd.id = gengxin.intd_id
where gengxin.time in (select max(time) from gengxin group by intd_id) and intd.xm7 like '小明'
union all
select * from intd
left join gengxin on intd.id = gengxin.intd_id
where gengxin.time is null and intd.xm1 like '小明'
union all
select * from intd
left join gengxin on intd.id = gengxin.intd_id
where gengxin.time is null and intd.xm7 like '小明'
) a order by a.time desc


3.0版：查询时间2秒
查询字段添加索引
alter table test add index(t_name);

然后使用2.0版的查询代码