从500万条数据中查询,求优化一条SQL语句

表结构如下,里面有500W数据。我没有权限修改这个表,所以请大家给我优化下SQL查询
[code="sql"]CREATE TABLE IF NOT EXISTS jdp_tb_trade (
tid bigint(20) NOT NULL,
status varchar(64) DEFAULT NULL,
type varchar(64) DEFAULT NULL,
seller_nick varchar(32) DEFAULT NULL,
buyer_nick varchar(32) DEFAULT NULL,
created datetime DEFAULT NULL,
modified datetime DEFAULT NULL,
jdp_hashcode varchar(128) DEFAULT NULL,
jdp_response mediumtext,
jdp_created datetime DEFAULT NULL,
jdp_modified datetime DEFAULT NULL,
PRIMARY KEY (tid),
KEY ind_jdp_tb_trade_seller_nick_jdp_modified (seller_nick,jdp_modified),
KEY ind_jdp_tb_trade_jdp_modified (jdp_modified),
KEY ind_jdp_tb_trade_seller_nick_modified (seller_nick,modified),
KEY ind_jdp_tb_trade_modified (modified)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;[/code]

符合以下条件的有3W条数据
[code="sql"]SELECT COUNT(*) AS tp_count FROM jdp_tb_trade WHERE ( seller_nick IN ('李心','zhixian50','陈鹏','雪儿','稀饭','婷婷','七七') ) AND ( (jdp_modified > '2007-11-30 09:52:39') AND (jdp_modified <= '2014-04-21 22:31:13') ) LIMIT 1[/code]

我要分页查询出这3W条数据,由于MYSQL分页越往后查询越慢,所以我用了关联查询。该查询在前面2页需要3到4分钟才能返回结果。后面的每页4秒左右就返回了。实在搞不明白为什么。麻烦大家帮忙优化下。每页必须要在10秒内完成。
[code="sql"]SELECT t1.jdp_modified,t1.jdp_response FROM jdp_tb_trade t1, ( SELECT tid FROM jdp_tb_trade WHERE ( seller_nick IN ('李心','zhixian50','陈鹏','雪儿','稀饭','婷婷','七七') ) AND ( (jdp_modified > '2007-11-30 09:52:39') AND (jdp_modified <= '2014-04-21 22:31:13') ) ORDER BY jdp_modified desc LIMIT 0,200 ) t2 WHERE t1.tid=t2.tid[/code]

12个回答

SELECT jdp_modified,jdp_response FROM jdp_tb_trade WHERE tid IN (select tid FROM jdp_tb_trade WHERE seller_nick IN ('李心','zhixian50','陈鹏','雪儿','稀饭','婷婷','七七')

AND jdp_modified > '2007-11-30 09:52:39'

AND jdp_modified <= '2014-04-21 22:31:13'

ORDER BY jdp_modified desc LIMIT 0,200 );

数据多还是得用索引

共12条数据 首页 2
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问