可是楼主什么信息都没提供啊。这样基本上没办法对你的设计进行评论。提供你的建表语句,查询语句。你可以到MYSQL的slow query 日志中查看一下执行花时间比较长的SQL语句都是哪些。show create table tabName; show index from tabName; explain select ......;请至少提供以上信息。
对了再问一下,my.ini的配置使用的内存设置成多少,适合这么大的数据量?
还有你说慢的语句的explain...贴出来看看吧
1 SQL 弄出来看看 2 SQL 关联表数据量也说说。
楼主是否能直接贴出文本,这样方便分析。show create table tabName; show index from tabName; explain select ......;把你慢的语句贴出来。
楼主是否能直接贴出文本,这样方便分析。show create table tabName; show index from tabName; explain select ......;把你慢的语句贴出来。
SELECT id,xiaoqu,househx,jz_area,housepay FROM esf_sell WHERE ifpass=1 AND (address like'%东丹庭院%') OR (xiaoqu like'%东丹庭院%') ORDER BY id desc LIMIT 10;SELECT id,xiaoqu,district,address,househx,housepay,jz_area,updatetime,UserType,iftuijian FROM esf_sell WHERE ifpass='1' ORDER BY updatetime DESC LIMIT 0, 30;可多语句呢,我不知道该怎么说,你有QQ吗?
SELECT id,xiaoqu,househx,jz_area,housepay FROM esf_sell WHERE ifpass=1 AND (address like'%东丹庭院%') OR (xiaoqu like'%东丹庭院%') ORDER BY id desc LIMIT 10;这个语句,没什么办法,你的 OR (xiaoqu like'%东丹庭院%') 一定会导致全表扫描。唯可利用的索引是 (ID) ,不知道你的表中有没有。 SELECT id,xiaoqu,district,address,househx,housepay,jz_area,updatetime,UserType,iftuijian FROM esf_sell WHERE ifpass='1' ORDER BY updatetime DESC LIMIT 0, 30; 这个查询看你有没有(ifpass,updatetime)的索引。如果没有要看你ifpass的数值分布。
好的,CREATE TABLE IF NOT EXISTS `esf_sell_1` ( `id` mediumint(8) NOT NULL auto_increment, `IsPublished` tinyint(1) NOT NULL default '1', `systemcode` varchar(100) NOT NULL default '' COMMENT '系统编号', `Internalcode` varchar(100) NOT NULL default '' COMMENT '内部编号', `district` varchar(20) NOT NULL, `TradeCircle` varchar(50) NOT NULL, `housefcz` int(4) NOT NULL, `zxcd` varchar(30) NOT NULL, `xiaoqu` varchar(100) NOT NULL, `address` varchar(100) NOT NULL, `househx` varchar(50) NOT NULL COMMENT '户型', `househx_s` tinyint(1) NOT NULL default '0', `househx_t` tinyint(1) NOT NULL default '0', `househx_w` tinyint(1) NOT NULL default '0', `househx_c` tinyint(1) NOT NULL default '0', `househx_y` tinyint(1) NOT NULL default '0', `houselx` varchar(30) NOT NULL COMMENT '房屋类型', `jz_area` float NOT NULL default '0' COMMENT '建筑面积', `housepay` float NOT NULL default '0' COMMENT '交易价格', `man` varchar(50) NOT NULL, `zjname` varchar(30) NOT NULL, `tel` varchar(100) NOT NULL, `UserID` mediumint(8) NOT NULL default '0' COMMENT '发布信息会员ID号', `UserType` varchar(2) NOT NULL default '个', `posttime` int(11) NOT NULL default '0' COMMENT '添加时间', `updatetime` int(11) NOT NULL default '0' COMMENT '更新时间', `iftuijian` tinyint(1) NOT NULL default '0' COMMENT '是否推荐', `admintuijian` tinyint(1) NOT NULL default '0' COMMENT '管理员推荐', `ifpass` tinyint(1) NOT NULL default '0' COMMENT '是否通过验证', `ifchengjiao` tinyint(1) NOT NULL default '0', `ifsetautoupdate` tinyint(1) NOT NULL default '0', `postip` varchar(15) NOT NULL, `pageviews` int(11) NOT NULL default '1', `ifdaikuan` varchar(10) NOT NULL, `chaoxiang` varchar(4) NOT NULL, `housejg` varchar(30) NOT NULL, `sy_area` float NOT NULL, `sum_floor` int(3) NOT NULL, `do_floor` int(3) NOT NULL, `jg_time` varchar(20) NOT NULL, `zawujian` varchar(50) NOT NULL, `ifxiaofang` varchar(20) NOT NULL, `kanfangtime` varchar(20) NOT NULL, `chanquan` varchar(30) NOT NULL, `peitao` varchar(255) NOT NULL, `tese` varchar(255) NOT NULL, `bus` varchar(100) NOT NULL, `email` varchar(100) NOT NULL, `qq` varchar(100) NOT NULL, `house_ms` mediumtext NOT NULL, `imageurl` char(200) NOT NULL, `imagenum` smallint(4) NOT NULL, `old_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `district` (`district`), KEY `TradeCircle` (`TradeCircle`), KEY `househx` (`househx`), KEY `updatetime` (`updatetime`), KEY `UserType` (`UserType`), KEY `UserID` (`UserID`), KEY `housepay` (`housepay`), KEY `jz_area` (`jz_area`), KEY `posttime` (`posttime`), KEY `ifpass` (`ifpass`) ) ENGINE=MyISAM DEFAULT CHARSET=gbk AUTO_INCREMENT=1
Query_time: 13 Lock_time: 0 Rows_sent: 30 Rows_examined: 225950 SELECT id,xiaoqu,district,address,househx,housepay,jz_area,updatetime,UserType,iftuijian FROM esf_sell WHERE ifpass='1' ORDER BY updatetime DESC LIMIT 0, 30; # Query_time: 17 Lock_time: 4 Rows_sent: 30 Rows_examined: 225950 SELECT id,xiaoqu,district,address,househx,housepay,jz_area,updatetime,UserType,iftuijian FROM esf_sell WHERE ifpass='1' ORDER BY updatetime DESC LIMIT 0, 30; # Query_time: 17 Lock_time: 4 Rows_sent: 30 Rows_examined: 225950 SELECT id,xiaoqu,district,address,househx,housepay,jz_area,updatetime,UserType,iftuijian FROM esf_sell WHERE ifpass='1' ORDER BY updatetime DESC LIMIT 0, 30;
到底是表 esf_sell_1 还是表 esf_sell ?已经讲过很多遍了。 请您贴一下 SELECT id,xiaoqu,district,address,househx,housepay,jz_area,updatetime,UserType,iftuijian FROM esf_sell WHERE ifpass='1' ORDER BY updatetime DESC LIMIT 0, 30; 的时间。及 explain SELECT id,xiaoqu,district,address,househx,housepay,jz_area,updatetime,UserType,iftuijian FROM esf_sell WHERE ifpass='1' ORDER BY updatetime DESC LIMIT 0, 30;的结果。
到底是表 esf_sell_1 还是表 esf_sell ?已经讲过很多遍了。 请您贴一下 SELECT id,xiaoqu,district,address,househx,housepay,jz_area,updatetime,UserType,iftuijian FROM esf_sell WHERE ifpass='1' ORDER BY updatetime DESC LIMIT 0, 30; 的时间。及 explain SELECT id,xiaoqu,district,address,househx,housepay,jz_area,updatetime,UserType,iftuijian FROM esf_sell WHERE ifpass='1' ORDER BY updatetime DESC LIMIT 0, 30;的结果。
那个数据结构能用不,能建上表吗?我是刚用mysql对这个不太懂,
CREATE TABLE `table`.`esf_sell` ( `id` mediumint( 8 ) NOT NULL auto_increment , `IsPublished` tinyint( 1 ) NOT NULL default '1', `systemcode` varchar( 100 ) NOT NULL default '' COMMENT '系统编号', `Internalcode` varchar( 100 ) NOT NULL default '' COMMENT '内部编号', `district` varchar( 20 ) NOT NULL , `TradeCircle` varchar( 50 ) NOT NULL , `housefcz` int( 4 ) NOT NULL , `zxcd` varchar( 30 ) NOT NULL , `xiaoqu` varchar( 100 ) NOT NULL , `address` varchar( 100 ) NOT NULL , `househx` varchar( 50 ) NOT NULL COMMENT '户型', `househx_s` tinyint( 1 ) NOT NULL default '0', `househx_t` tinyint( 1 ) NOT NULL default '0', `househx_w` tinyint( 1 ) NOT NULL default '0', `househx_c` tinyint( 1 ) NOT NULL default '0', `househx_y` tinyint( 1 ) NOT NULL default '0', `houselx` varchar( 30 ) NOT NULL COMMENT '房屋类型', `jz_area` float NOT NULL default '0' COMMENT '建筑面积', `housepay` float NOT NULL default '0' COMMENT '交易价格', `man` varchar( 50 ) NOT NULL , `zjname` varchar( 30 ) NOT NULL , `tel` varchar( 100 ) NOT NULL , `UserID` mediumint( 8 ) NOT NULL default '0' COMMENT '发布信息会员ID号', `UserType` varchar( 2 ) NOT NULL default '个', `posttime` int( 11 ) NOT NULL default '0' COMMENT '添加时间', `updatetime` int( 11 ) NOT NULL default '0' COMMENT '更新时间', `iftuijian` tinyint( 1 ) NOT NULL default '0' COMMENT '是否推荐', `admintuijian` tinyint( 1 ) NOT NULL default '0' COMMENT '管理员推荐', `ifpass` tinyint( 1 ) NOT NULL default '0' COMMENT '是否通过验证', `ifchengjiao` tinyint( 1 ) NOT NULL default '0', `ifsetautoupdate` tinyint( 1 ) NOT NULL default '0', `postip` varchar( 15 ) NOT NULL , `pageviews` int( 11 ) NOT NULL default '1', `ifdaikuan` varchar( 10 ) NOT NULL , `chaoxiang` varchar( 4 ) NOT NULL , `housejg` varchar( 30 ) NOT NULL , `sy_area` float NOT NULL , `sum_floor` int( 3 ) NOT NULL , `do_floor` int( 3 ) NOT NULL , `jg_time` varchar( 20 ) NOT NULL , `zawujian` varchar( 50 ) NOT NULL , `ifxiaofang` varchar( 20 ) NOT NULL , `kanfangtime` varchar( 20 ) NOT NULL , `chanquan` varchar( 30 ) NOT NULL , `peitao` varchar( 255 ) NOT NULL , `tese` varchar( 255 ) NOT NULL , `bus` varchar( 100 ) NOT NULL , `email` varchar( 100 ) NOT NULL , `qq` varchar( 100 ) NOT NULL , `house_ms` mediumtext NOT NULL , `imageurl` char( 200 ) NOT NULL , `imagenum` smallint( 4 ) NOT NULL , `old_id` int( 11 ) NOT NULL , PRIMARY KEY ( `id` ) , KEY `district` ( `district` ) , KEY `TradeCircle` ( `TradeCircle` ) , KEY `househx` ( `househx` ) , KEY `updatetime` ( `updatetime` ) , KEY `UserType` ( `UserType` ) , KEY `UserID` ( `UserID` ) , KEY `housepay` ( `housepay` ) , KEY `jz_area` ( `jz_area` ) , KEY `posttime` ( `posttime` ) , KEY `ifpass` ( `ifpass` , `district` ( 4 ) , `UserType` ) ) ENGINE = MyISAM DEFAULT CHARSET = gbk;
show index from tabName;这个我弄成图片你看行吗?
show index from esf_sell;然后贴出结果!SELECT id,xiaoqu,district,address,househx,housepay,jz_area,updatetime,UserType,iftuijian FROM esf_sell WHERE ifpass='1' ORDER BY updatetime DESC LIMIT 0, 30; 贴出运行时间的结果!explain SELECT id,xiaoqu,district,address,househx,housepay,jz_area,updatetime,UserType,iftuijian FROM esf_sell WHERE ifpass='1' ORDER BY updatetime DESC LIMIT 0, 30; 然后贴出的结果。
show index from esf_sell;
SHOW INDEX FROM esf_sell;执行结果:Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment esf_sell 0 PRIMARY 1 id A 226604 NULL NULL BTREE esf_sell 1 district 1 district A 20 NULL NULL BTREE esf_sell 1 TradeCircle 1 TradeCircle A 197 NULL NULL BTREE esf_sell 1 househx 1 househx A 25 NULL NULL BTREE esf_sell 1 updatetime 1 updatetime A 113302 NULL NULL BTREE esf_sell 1 UserType 1 UserType A 3 NULL NULL BTREE esf_sell 1 UserID 1 UserID A 4046 NULL NULL BTREE esf_sell 1 housepay 1 housepay A 2360 NULL NULL BTREE esf_sell 1 jz_area 1 jz_area A 5035 NULL NULL BTREE esf_sell 1 posttime 1 posttime A 226604 NULL NULL BTREE esf_sell 1 ifpass 1 ifpass A 2 NULL NULL BTREE esf_sell 1 ifpass 2 district A 21 4 NULL BTREE esf_sell 1 ifpass 3 UserType A 48 NULL NULL BTREE
EXPLAIN SELECT id, xiaoqu, district, address, househx, housepay, jz_area, updatetime, UserType, iftuijian FROM esf_sell WHERE ifpass = '1' ORDER BY updatetime DESC LIMIT 0 , 30;执行结果: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE esf_sell range ifpass ifpass 1 NULL 226546 Using where; Using filesort
SELECT id,xiaoqu,district,address,househx,housepay,jz_area,updatetime,UserType,iftuijian FROM esf_sell WHERE ifpass='1' ORDER BY updatetime DESC LIMIT 0, 30; 执行时间:显示行 0 - 29 (30 总计, 查询花费 1.5111 秒)
贴出如下语句的输出! EXPLAIN SELECT id, xiaoqu, district, address, househx, housepay, jz_area, updatetime, UserType, iftuijian FROM esf_sell FORCE INDEX (updatetime) IGNORE INDEX (ifpass) WHERE ifpass = '1' ORDER BY updatetime DESC LIMIT 0 , 30;贴出如下语句的执行时间。SELECT id, xiaoqu, district, address, househx, housepay, jz_area, updatetime, UserType, iftuijian FROM esf_sell FORCE INDEX (updatetime) IGNORE INDEX (ifpass) WHERE ifpass = '1' ORDER BY updatetime DESC LIMIT 0 , 30;
SELECT id, xiaoqu, district, address, househx, housepay, jz_area, updatetime, UserType, iftuijian FROM esf_sell FORCE INDEX (updatetime) IGNORE INDEX (ifpass) WHERE ifpass = '1' ORDER BY updatetime DESC LIMIT 0 , 30;执行不了,出现错误: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE INDEX (ifpass) WHERE ifpass = '1' ORDER BY updatetime DESC LIMIT 0 ,' at line 2
SELECT id, xiaoqu, district, address, househx, housepay, jz_area, updatetime, UserType, iftuijian FROM esf_sell FORCE INDEX ( updatetime ) WHERE ifpass = '1' ORDER BY updatetime DESC LIMIT 0 , 30;显示行 0 - 29 (30 总计, 查询花费 0.2504 秒)
你的mysql 版本到是多少?mysql> SELECT id, xiaoqu, district, address, househx, housepay, jz_area, updatet ime, UserType, iftuijian -> FROM esf_sell_1 FORCE INDEX (updatetime) IGNORE INDEX (ifpass) -> WHERE ifpass = '1' -> ORDER BY updatetime DESC -> LIMIT 0 , 30; Empty set (0.00 sec)mysql>另外 查询花费 0.2504 秒 ,应该可以满足你一般要求了。
Select id,district,xiaoqu,househx_s,househx_t,househx_w,jz_area,housepay,man,zjname,UserID,updatetime From esf_sell WHERE ifpass=1 AND UserType='中' AND district='新华区' GROUP BY UserID ORDER BY updatetime DESC LIMIT 30; 还有,下面这些是搜索的条件: if ($uid) { $sqlsrch.=" AND UserID='$uid'"; } if ($sq) { $sqlsrch.=" AND TradeCircle='$tradecircle'"; } if ($zj) { $sqlsrch.=" AND UserType='$zj'"; } if ($hx) { if ($hx>5) { $sqlsrch.=" AND househx_s>'$hx'"; }else { $sqlsrch.=" AND househx_s='$hx'"; } } if ($fwlx) { $sqlsrch.=" AND houselx='$fwlx'"; } if ($min_jg) { $sqlsrch.=" AND housepay>=$min_jg"; } if ($max_jg) { $sqlsrch.=" AND housepay<=$max_jg"; } if ($min_area) { $sqlsrch.=" AND jz_area>=$min_area"; } if ($max_area) { $sqlsrch.=" AND jz_area<=$max_area"; } if ($addr) { $sqlsrch.=" AND (address LIKE '%$addr%' OR xiaoqu LIKE '%$addr%')"; }
UPDATE esf_sell SET updatetime={$timestamp} WHERE id IN ($id);
show index from tabName;
explain select ......;请至少提供以上信息。
2 SQL 关联表数据量也说说。
show index from tabName;
explain select ......;把你慢的语句贴出来。
show index from tabName;
explain select ......;把你慢的语句贴出来。
至于慢的SQL语句,先一个一个解决。 比如你 18楼这个语句,现在执行花了多少时间?
然后用
explain select ...看一下,贴出结果。
FROM esf_sell
WHERE ifpass=1 AND (address like'%东丹庭院%')
OR (xiaoqu like'%东丹庭院%')
ORDER BY id desc
LIMIT 10;这个语句,没什么办法,你的 OR (xiaoqu like'%东丹庭院%') 一定会导致全表扫描。唯可利用的索引是 (ID) ,不知道你的表中有没有。
SELECT id,xiaoqu,district,address,househx,housepay,jz_area,updatetime,UserType,iftuijian
FROM esf_sell
WHERE ifpass='1'
ORDER BY updatetime DESC LIMIT 0, 30;
这个查询看你有没有(ifpass,updatetime)的索引。如果没有要看你ifpass的数值分布。
`id` mediumint(8) NOT NULL auto_increment,
`IsPublished` tinyint(1) NOT NULL default '1',
`systemcode` varchar(100) NOT NULL default '' COMMENT '系统编号',
`Internalcode` varchar(100) NOT NULL default '' COMMENT '内部编号',
`district` varchar(20) NOT NULL,
`TradeCircle` varchar(50) NOT NULL,
`housefcz` int(4) NOT NULL,
`zxcd` varchar(30) NOT NULL,
`xiaoqu` varchar(100) NOT NULL,
`address` varchar(100) NOT NULL,
`househx` varchar(50) NOT NULL COMMENT '户型',
`househx_s` tinyint(1) NOT NULL default '0',
`househx_t` tinyint(1) NOT NULL default '0',
`househx_w` tinyint(1) NOT NULL default '0',
`househx_c` tinyint(1) NOT NULL default '0',
`househx_y` tinyint(1) NOT NULL default '0',
`houselx` varchar(30) NOT NULL COMMENT '房屋类型',
`jz_area` float NOT NULL default '0' COMMENT '建筑面积',
`housepay` float NOT NULL default '0' COMMENT '交易价格',
`man` varchar(50) NOT NULL,
`zjname` varchar(30) NOT NULL,
`tel` varchar(100) NOT NULL,
`UserID` mediumint(8) NOT NULL default '0' COMMENT '发布信息会员ID号',
`UserType` varchar(2) NOT NULL default '个',
`posttime` int(11) NOT NULL default '0' COMMENT '添加时间',
`updatetime` int(11) NOT NULL default '0' COMMENT '更新时间',
`iftuijian` tinyint(1) NOT NULL default '0' COMMENT '是否推荐',
`admintuijian` tinyint(1) NOT NULL default '0' COMMENT '管理员推荐',
`ifpass` tinyint(1) NOT NULL default '0' COMMENT '是否通过验证',
`ifchengjiao` tinyint(1) NOT NULL default '0',
`ifsetautoupdate` tinyint(1) NOT NULL default '0',
`postip` varchar(15) NOT NULL,
`pageviews` int(11) NOT NULL default '1',
`ifdaikuan` varchar(10) NOT NULL,
`chaoxiang` varchar(4) NOT NULL,
`housejg` varchar(30) NOT NULL,
`sy_area` float NOT NULL,
`sum_floor` int(3) NOT NULL,
`do_floor` int(3) NOT NULL,
`jg_time` varchar(20) NOT NULL,
`zawujian` varchar(50) NOT NULL,
`ifxiaofang` varchar(20) NOT NULL,
`kanfangtime` varchar(20) NOT NULL,
`chanquan` varchar(30) NOT NULL,
`peitao` varchar(255) NOT NULL,
`tese` varchar(255) NOT NULL,
`bus` varchar(100) NOT NULL,
`email` varchar(100) NOT NULL,
`qq` varchar(100) NOT NULL,
`house_ms` mediumtext NOT NULL,
`imageurl` char(200) NOT NULL,
`imagenum` smallint(4) NOT NULL,
`old_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `district` (`district`),
KEY `TradeCircle` (`TradeCircle`),
KEY `househx` (`househx`),
KEY `updatetime` (`updatetime`),
KEY `UserType` (`UserType`),
KEY `UserID` (`UserID`),
KEY `housepay` (`housepay`),
KEY `jz_area` (`jz_area`),
KEY `posttime` (`posttime`),
KEY `ifpass` (`ifpass`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk AUTO_INCREMENT=1
SELECT id,xiaoqu,district,address,househx,housepay,jz_area,updatetime,UserType,iftuijian FROM esf_sell WHERE ifpass='1' ORDER BY updatetime DESC LIMIT 0, 30;
# Query_time: 17 Lock_time: 4 Rows_sent: 30 Rows_examined: 225950
SELECT id,xiaoqu,district,address,househx,housepay,jz_area,updatetime,UserType,iftuijian FROM esf_sell WHERE ifpass='1' ORDER BY updatetime DESC LIMIT 0, 30;
# Query_time: 17 Lock_time: 4 Rows_sent: 30 Rows_examined: 225950
SELECT id,xiaoqu,district,address,househx,housepay,jz_area,updatetime,UserType,iftuijian FROM esf_sell WHERE ifpass='1' ORDER BY updatetime DESC LIMIT 0, 30;
到底是表 esf_sell_1 还是表 esf_sell ?已经讲过很多遍了。
请您贴一下
SELECT id,xiaoqu,district,address,househx,housepay,jz_area,updatetime,UserType,iftuijian FROM esf_sell WHERE ifpass='1' ORDER BY updatetime DESC LIMIT 0, 30;
的时间。及 explain SELECT id,xiaoqu,district,address,househx,housepay,jz_area,updatetime,UserType,iftuijian FROM esf_sell WHERE ifpass='1' ORDER BY updatetime DESC LIMIT 0, 30;的结果。
到底是表 esf_sell_1 还是表 esf_sell ?已经讲过很多遍了。
请您贴一下
SELECT id,xiaoqu,district,address,househx,housepay,jz_area,updatetime,UserType,iftuijian FROM esf_sell WHERE ifpass='1' ORDER BY updatetime DESC LIMIT 0, 30;
的时间。及 explain SELECT id,xiaoqu,district,address,househx,housepay,jz_area,updatetime,UserType,iftuijian FROM esf_sell WHERE ifpass='1' ORDER BY updatetime DESC LIMIT 0, 30;的结果。
`IsPublished` tinyint( 1 ) NOT NULL default '1',
`systemcode` varchar( 100 ) NOT NULL default '' COMMENT '系统编号',
`Internalcode` varchar( 100 ) NOT NULL default '' COMMENT '内部编号',
`district` varchar( 20 ) NOT NULL ,
`TradeCircle` varchar( 50 ) NOT NULL ,
`housefcz` int( 4 ) NOT NULL ,
`zxcd` varchar( 30 ) NOT NULL ,
`xiaoqu` varchar( 100 ) NOT NULL ,
`address` varchar( 100 ) NOT NULL ,
`househx` varchar( 50 ) NOT NULL COMMENT '户型',
`househx_s` tinyint( 1 ) NOT NULL default '0',
`househx_t` tinyint( 1 ) NOT NULL default '0',
`househx_w` tinyint( 1 ) NOT NULL default '0',
`househx_c` tinyint( 1 ) NOT NULL default '0',
`househx_y` tinyint( 1 ) NOT NULL default '0',
`houselx` varchar( 30 ) NOT NULL COMMENT '房屋类型',
`jz_area` float NOT NULL default '0' COMMENT '建筑面积',
`housepay` float NOT NULL default '0' COMMENT '交易价格',
`man` varchar( 50 ) NOT NULL ,
`zjname` varchar( 30 ) NOT NULL ,
`tel` varchar( 100 ) NOT NULL ,
`UserID` mediumint( 8 ) NOT NULL default '0' COMMENT '发布信息会员ID号',
`UserType` varchar( 2 ) NOT NULL default '个',
`posttime` int( 11 ) NOT NULL default '0' COMMENT '添加时间',
`updatetime` int( 11 ) NOT NULL default '0' COMMENT '更新时间',
`iftuijian` tinyint( 1 ) NOT NULL default '0' COMMENT '是否推荐',
`admintuijian` tinyint( 1 ) NOT NULL default '0' COMMENT '管理员推荐',
`ifpass` tinyint( 1 ) NOT NULL default '0' COMMENT '是否通过验证',
`ifchengjiao` tinyint( 1 ) NOT NULL default '0',
`ifsetautoupdate` tinyint( 1 ) NOT NULL default '0',
`postip` varchar( 15 ) NOT NULL ,
`pageviews` int( 11 ) NOT NULL default '1',
`ifdaikuan` varchar( 10 ) NOT NULL ,
`chaoxiang` varchar( 4 ) NOT NULL ,
`housejg` varchar( 30 ) NOT NULL ,
`sy_area` float NOT NULL ,
`sum_floor` int( 3 ) NOT NULL ,
`do_floor` int( 3 ) NOT NULL ,
`jg_time` varchar( 20 ) NOT NULL ,
`zawujian` varchar( 50 ) NOT NULL ,
`ifxiaofang` varchar( 20 ) NOT NULL ,
`kanfangtime` varchar( 20 ) NOT NULL ,
`chanquan` varchar( 30 ) NOT NULL ,
`peitao` varchar( 255 ) NOT NULL ,
`tese` varchar( 255 ) NOT NULL ,
`bus` varchar( 100 ) NOT NULL ,
`email` varchar( 100 ) NOT NULL ,
`qq` varchar( 100 ) NOT NULL ,
`house_ms` mediumtext NOT NULL ,
`imageurl` char( 200 ) NOT NULL ,
`imagenum` smallint( 4 ) NOT NULL ,
`old_id` int( 11 ) NOT NULL ,
PRIMARY KEY ( `id` ) ,
KEY `district` ( `district` ) ,
KEY `TradeCircle` ( `TradeCircle` ) ,
KEY `househx` ( `househx` ) ,
KEY `updatetime` ( `updatetime` ) ,
KEY `UserType` ( `UserType` ) ,
KEY `UserID` ( `UserID` ) ,
KEY `housepay` ( `housepay` ) ,
KEY `jz_area` ( `jz_area` ) ,
KEY `posttime` ( `posttime` ) ,
KEY `ifpass` ( `ifpass` , `district` ( 4 ) , `UserType` ) ) ENGINE = MyISAM DEFAULT CHARSET = gbk;
然后贴出的结果。
esf_sell 0 PRIMARY 1 id A 226604 NULL NULL BTREE
esf_sell 1 district 1 district A 20 NULL NULL BTREE
esf_sell 1 TradeCircle 1 TradeCircle A 197 NULL NULL BTREE
esf_sell 1 househx 1 househx A 25 NULL NULL BTREE
esf_sell 1 updatetime 1 updatetime A 113302 NULL NULL BTREE
esf_sell 1 UserType 1 UserType A 3 NULL NULL BTREE
esf_sell 1 UserID 1 UserID A 4046 NULL NULL BTREE
esf_sell 1 housepay 1 housepay A 2360 NULL NULL BTREE
esf_sell 1 jz_area 1 jz_area A 5035 NULL NULL BTREE
esf_sell 1 posttime 1 posttime A 226604 NULL NULL BTREE
esf_sell 1 ifpass 1 ifpass A 2 NULL NULL BTREE
esf_sell 1 ifpass 2 district A 21 4 NULL BTREE
esf_sell 1 ifpass 3 UserType A 48 NULL NULL BTREE
FROM esf_sell
WHERE ifpass = '1'
ORDER BY updatetime DESC
LIMIT 0 , 30;执行结果: id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE esf_sell range ifpass ifpass 1 NULL 226546 Using where; Using filesort
EXPLAIN
SELECT id, xiaoqu, district, address, househx, housepay, jz_area, updatetime, UserType, iftuijian
FROM esf_sell FORCE INDEX (updatetime) IGNORE INDEX (ifpass)
WHERE ifpass = '1'
ORDER BY updatetime DESC
LIMIT 0 , 30;贴出如下语句的执行时间。SELECT id, xiaoqu, district, address, househx, housepay, jz_area, updatetime, UserType, iftuijian
FROM esf_sell FORCE INDEX (updatetime) IGNORE INDEX (ifpass)
WHERE ifpass = '1'
ORDER BY updatetime DESC
LIMIT 0 , 30;
FROM esf_sell FORCE INDEX (updatetime) IGNORE INDEX (ifpass)
WHERE ifpass = '1'
ORDER BY updatetime DESC
LIMIT 0 , 30;执行不了,出现错误:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE INDEX (ifpass)
WHERE ifpass = '1'
ORDER BY updatetime DESC
LIMIT 0 ,' at line 2
FROM esf_sell
FORCE INDEX ( updatetime )
WHERE ifpass = '1'
ORDER BY updatetime DESC
LIMIT 0 , 30;显示行 0 - 29 (30 总计, 查询花费 0.2504 秒)
ime, UserType, iftuijian
-> FROM esf_sell_1 FORCE INDEX (updatetime) IGNORE INDEX (ifpass)
-> WHERE ifpass = '1'
-> ORDER BY updatetime DESC
-> LIMIT 0 , 30;
Empty set (0.00 sec)mysql>另外 查询花费 0.2504 秒 ,应该可以满足你一般要求了。
很多问题没有办法直接回答你,除非你能提供更多,更详细的信息。更新,添加,删除的比较频繁,就会慢,主要更新updatetime这个字段
索引多会对update/insert/delete有影响,但会提高select / update / delete 的速度。这个在不知道你的具体语句的时候,没办法做什么评论。
问题说明越详细,回答也会越准确!参见如何提问。(提问的智慧)
还有,下面这些是搜索的条件:
if ($uid) {
$sqlsrch.=" AND UserID='$uid'";
}
if ($sq) {
$sqlsrch.=" AND TradeCircle='$tradecircle'";
}
if ($zj) {
$sqlsrch.=" AND UserType='$zj'";
}
if ($hx) {
if ($hx>5) {
$sqlsrch.=" AND househx_s>'$hx'";
}else {
$sqlsrch.=" AND househx_s='$hx'";
}
}
if ($fwlx) {
$sqlsrch.=" AND houselx='$fwlx'";
}
if ($min_jg) {
$sqlsrch.=" AND housepay>=$min_jg";
}
if ($max_jg) {
$sqlsrch.=" AND housepay<=$max_jg";
}
if ($min_area) {
$sqlsrch.=" AND jz_area>=$min_area";
}
if ($max_area) {
$sqlsrch.=" AND jz_area<=$max_area";
}
if ($addr) {
$sqlsrch.=" AND (address LIKE '%$addr%' OR xiaoqu LIKE '%$addr%')";
}
2。用EXPALN来查看MYSQL的实际执行计划。
3。对比分析,找出不正确的部分,然后进行人为调整,比如添加索引或优化你的SQL语句。不可能通过这个贴子这种方式帮你一个一个语句去分析,判断。关键是你自己要通过类似的方法找到解决问题的途径。