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

> 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 <tianjing@stripped>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 <johnny@stripped>
>>
>>> 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 <tianjing@stripped>
>>> 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 <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
>>> >
>>> >
>>>
>>>
>>> --
>>> -----------------------------
>>> Johnny Withers
>>> 601.209.4985
>>> johnny@stripped
>>>
>>
>>
>>
>> --
>>  Tianjing
>>
>> Bioinformatics Center,
>> Beijing Genomics Institute,Shenzhen
>> Tel:+86-755-25273851
>> MSN:tianjing217@stripped <MSN%3Atianjing217@stripped>
>>
>
>
>
> --
> -----------------------------
> Johnny Withers
> 601.209.4985
> johnny@stripped
>



-- 
Tianjing

Bioinformatics Center,
Beijing Genomics Institute,Shenzhen
Tel:+86-755-25273851
MSN:tianjing217@stripped <MSN%3Atianjing217@stripped>

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