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

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