sorry fo that, but i really need all cols in the table, i think the problem maybe caused by one of the col which is text type, each record of this col has 2000 characters. this makes the size of record more biger.
2009/7/13 Darryle Steplight <dstepli...@gmail.com> > You are still doing SELECT * . Do you really need to return all of the > columns in that table or just COL1, COL2, COL5 for example. Only grab > the columns you are actually going to use. > > On Mon, Jul 13, 2009 at 12:23 AM, TianJing<tianj...@genomics.org.cn> > wrote: > > thanks for reply, > > > > i hava an index on the start_position,the min_postion and the max_postion > is > > constant value, the output of the query is: > > > > explain select * from REF_SEQ where START_POSITION between 30000 and > > 8030000; > > > > > +----+-------------+---------+-------+-----------------+-----------------+---------+------+-------+-------------+ > > | id | select_type | table | type | possible_keys | key > | > > key_len | ref | rows | Extra | > > > +----+-------------+---------+-------+-----------------+-----------------+---------+------+-------+-------------+ > > | 1 | SIMPLE | REF_SEQ | range | index_seq_start | index_seq_start > | > > 5 | NULL | 90886 | Using where | > > > +----+-------------+---------+-------+-----------------+-----------------+---------+------+-------+-------------+ > > > > index_seq_start is the index on start_postion, > > > > 2009/7/13 Darryle Steplight <dstepli...@gmail.com> > >> > >> 1. Don't use SELECT *. Only grab the cols that you only need. Also > >> make sure you have an index on min_position and max_position. After > >> that if your query isn't faster please show us the output of running > >> EXPLAIN select * from table_name where start_postion between > >> min_postion and > >> max_postion" . > >> > >> On Mon, Jul 13, 2009 at 12:03 AM, JingTian<jingtian.seu...@gmail.com> > >> wrote: > >> > Hi all, > >> > > >> > i use "select * from table_name where start_postion between > min_postion > >> > and > >> > max_postion" to select all the record in the ranges, > >> > when the ranges is very large,such as 8000000(about 1000 record in > it), > >> > the > >> > query is so slow, > >> > > >> > when i use mysql administrator i find that traffic is higher when the > >> > query > >> > is begin, > >> > > >> > could you please give me some advice on how to optimization the query? > >> > > >> > thanks, > >> > > >> > -- > >> > Tianjing > >> > > >> > >> > >> > >> -- > >> A: It reverses the normal flow of conversation. > >> Q: What's wrong with top-posting? > >> A: Top-posting. > >> Q: What's the biggest scourge on plain text email discussions? > > > > > > > > -- > > > -- > A: It reverses the normal flow of conversation. > Q: What's wrong with top-posting? > A: Top-posting. > Q: What's the biggest scourge on plain text email discussions? > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=jingtian.seu...@gmail.com > > -- Tianjing
