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