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

[小熊开发]

表结构如下，里面有500W数据。我没有权限修改这个表，所以请大家给我优化下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;

符合以下条件的有3W条数据

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

我要分页查询出这3W条数据，由于MYSQL分页越往后查询越慢，所以我用了关联查询。该查询在前面2页需要3到4分钟才能返回结果。后面的每页4秒左右就返回了。实在搞不明白为什么。麻烦大家帮忙优化下。每页必须要在10秒内完成。

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

[ayzen1988]

1、

SELECT jdp_modified,jdp_response 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 $start,$limit

2、 如果数据不需要很及时的话 先把符合条件的数据插入到临时表当中、在临时表中做分页

[壮壮老师]

我提个建议，可以先查询出前面的3000条，分页，这样点击比较快，如果客户需要看后面的，点击之后查后面的3000条，以此往复