Can you show the CREATE TABLE for your REF_SEQ table? The explain output says "using where" which means that MySQL will have to post-filter rows after the storage engine retrieves them. It also means the query may benefit from different/better indexing.
On Mon, Jul 13, 2009 at 12:04 AM, TianJing <tianj...@genomics.org.cn> wrote: > 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> < > msn%3atianjing...@hotmail.com <msn%253atianjing...@hotmail.com>> > -- ----------------------------- Johnny Withers 601.209.4985 joh...@pixelated.net
