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



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