List:General Discussion« Previous MessageNext Message »
From:Johnny Withers Date:July 13 2009 4:31pm
Subject:Re: mysql select query
View as plain text  
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

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