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

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