表结构,共三千多条记录。而查询语句却要差不多一分钟……哪个能告诉我为什么吗?
create table provinces (
p_id int(6) primary key,
p_name char(40),
p_cid int(6),
p_ptid int(6),
p_sort char(40)
)ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=gbk;create table citys(
c_id int(6) primary key,
c_name char(40),
c_pid int(6),
c_parentid int(6)
)ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=gbk;
查询语句
select p_name,c_name
from provinces ,citys
where c_name in (
select c_name from citys where c_name like ('%番%')
) AND p_id = c_pid
order by p_name
create table provinces (
p_id int(6) primary key,
p_name char(40),
p_cid int(6),
p_ptid int(6),
p_sort char(40)
)ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=gbk;create table citys(
c_id int(6) primary key,
c_name char(40),
c_pid int(6),
c_parentid int(6)
)ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=gbk;
查询语句
select p_name,c_name
from provinces ,citys
where c_name in (
select c_name from citys where c_name like ('%番%')
) AND p_id = c_pid
order by p_name
from provinces ,citys
where c_name in (
select c_name from citys where c_name like ('%番%')
) AND p_id = c_pid
order by p_name
看看走索引没
在c_name上建立索引试试,将IN修改为连接
from provinces
inner join citys
on p_id = c_pid
where c_name in (
select c_name from citys where c_name like ('%番%')
)
order by p_name
select p.p_name,c.c_name
from provinces p ,citys c
where p.p_id = c.c_pid
and c.c_name like('%番%')
order by p.p_name;改成这样试试?看快一点不?在provinces表的p_id字段建立索引,citys表的c_pid字段建立索引。