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
