i do not use text for start_postion,i use int for it. the only col which defined to text is characters such as "ABTGDSDFSGFDG" etc.
2009/7/13 Darryle Steplight <dstepli...@gmail.com> > Numeric indexing is a lot faster. You definitely shouldn't use text or > varchar types as column types for you min and max values. Do an ALTER > TABLE on any column only hold numeric values and switch them to int > or mediumint. > > On Mon, Jul 13, 2009 at 12:36 AM, TianJing<tianj...@genomics.org.cn> > wrote: > > 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 > > > > > > > > -- > 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>
