sorry for my careless,the sql should be select * from REF_SEQ where REF_ID = 3 and START_POSITION between 30000 and 8030000;
the explain output is : mysql> explain select * from REF_SEQ where REF_ID = 3 and 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_ref_start | index_ref_start | 8 | NULL | 2408 | Using where | +----+-------------+---------+-------+-----------------+-----------------+---------+------+------+-------------+ in this sql,the index is on REF_ID and START_POSITION, the rows in the output is more less than that index_POS on START_POSITION and index_ref on REF_ID. 2009/7/13 Johnny Withers <joh...@pixelated.net> > I see that index_ref_start is defined on Ref_Id and Start_Position. Mysql > only uses the left-most column of this index. Drop and re-add this key only > defined as > > INDEX idx_ref_start(start_position) > > and see if that helps. > > Your explain you sent this time is not even using the index. > > In your previous explain output, mysql said the key_len is 5. Since both > columns in this key are INT (4-bytes), it says it's only using the > left-most > column, REF_ID. I'm not sure why it says 5 and not 4, maybe someone else > can > explain this. > > I'd redefine the index to only use the a single column, then define a new > index on REF_ID if you use that in JOINs. > > > > On Mon, Jul 13, 2009 at 9:07 AM, TianJing <tianj...@genomics.org.cn> > wrote: > > > the REF_SEQ is defined below, the col DNA_SEQ is a string such as > > "ATGCGGTTA", > > > > | REF_SEQ | CREATE TABLE `REF_SEQ` ( > > `SEQ_ID` int(11) NOT NULL auto_increment, > > `REF_ID` int(11) NOT NULL, > > `START_POSITION` int(11) NOT NULL, > > `END_POSITION` int(11) NOT NULL, > > `DNA_SEQ` text, > > `DNA_QUALITY` text, > > PRIMARY KEY (`SEQ_ID`), > > KEY `index_ref_start` (`REF_ID`,`START_POSITION`) > > ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | > > > > i create a index on cols REF_ID and START_POSITION, i also use analyze > > table REF_SEQ to optimization the query, > > and now the explain output is: > > > > > > mysql> 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 | ALL | NULL | NULL | NULL | > NULL > > | 219728 | Using where | > > > > > +----+-------------+---------+------+---------------+------+---------+------+--------+-------------+ > > > > > > > > > > 2009/7/13 Johnny Withers <joh...@pixelated.net> > > > >> 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? > >>> > > >>> > >>> > >>> > >> -- > >> ----------------------------- > >> Johnny Withers > >> 601.209.4985 > >> joh...@pixelated.net > >> > > > > > > > > -- > > Tianjing > > > > > > > -- > ----------------------------- > Johnny Withers > 601.209.4985 > joh...@pixelated.net > -- Tianjing Bioinformatics Center, Beijing Genomics Institute,Shenzhen Tel:+86-755-25273851 MSN:tianjing...@hotmail.com <msn%3atianjing...@hotmail.com>
