yes,it is more faster that i select every cols except the TEXT col,but unfortunately i need the TEXT cols for next step.
2009/7/14 Johnny Withers <joh...@pixelated.net> > 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 > -- Tianjing Bioinformatics Center, Beijing Genomics Institute,Shenzhen Tel:+86-755-25273851 MSN:tianjing...@hotmail.com <msn%3atianjing...@hotmail.com>
