List:General Discussion« Previous MessageNext Message »
From:TianJing Date:July 13 2009 2:07pm
Subject:Re: mysql select query
View as plain text  
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 <johnny@stripped>

> 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 <tianjing@stripped>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 <dsteplight@stripped>
>>
>> > 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<tianjing@stripped>
>> > 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 <dsteplight@stripped>
>> > >>
>> > >> 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<tianjing@stripped>
>> > >> 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 <dsteplight@stripped>
>> > >> >>
>> > >> >> 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.seu217@stripped
>> > >
>> > >> >> 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=1
>> > >>
>> > >
>> > >
>> > >
>> > > --
>> > > 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
> johnny@stripped
>



-- 
Tianjing

Thread
mysql select queryJingTian13 Jul
  • Re: mysql select queryDarryle Steplight13 Jul
    • Re: mysql select queryTianJing13 Jul
      • Re: mysql select queryDarryle Steplight13 Jul
        • Re: mysql select queryTianJing13 Jul
          • Re: mysql select queryDarryle Steplight13 Jul
            • Re: mysql select queryTianJing13 Jul
              • Re: mysql select queryJohnny Withers13 Jul
                • Re: mysql select queryTianJing13 Jul
                  • Re: mysql select queryJohnny Withers13 Jul
                    • Re: mysql select queryTianJing13 Jul
                      • Re: mysql select queryJohnny Withers13 Jul
                        • Re: mysql select queryTianJing14 Jul