It looks like MySQL is using both columns in the key for that query, since the key_len is 8, but for some reason it says it is still "using where".
What happens when you only select these fields: seq_id, ref_id, start_position, end_position? Does the query speed up? I had a table that had some TEXT columns defined and I found when I selected every column excep the TEXT column the query ran faster. On Mon, Jul 13, 2009 at 9:45 AM, TianJing <tianj...@genomics.org.cn> wrote: > 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> > -- ----------------------------- Johnny Withers 601.209.4985 joh...@pixelated.net
