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



-- 
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