List:General Discussion« Previous MessageNext Message »
From:Darryle Steplight Date:July 13 2009 4:28am
Subject:Re: mysql select query
View as plain text  
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?
>
>
>
> --
> Tianjing
>
> Bioinformatics Center,
> Beijing Genomics Institute,Shenzhen
> Tel:+86-755-25273851
> MSN:tianjing217@stripped
>



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