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? > -- Tianjing Bioinformatics Center, Beijing Genomics Institute,Shenzhen Tel:+86-755-25273851 MSN:tianjing...@hotmail.com <msn%3atianjing...@hotmail.com>
