From: Johnny Withers (johnnypixelated.net)
Date: Mon Jul 13 2009 - 08:44:58 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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 <tianjinggenomics.org.cn> 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 <dsteplightgmail.com>
>
> > 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<tianjinggenomics.org.cn>
> > 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 <dsteplightgmail.com>
> > >>
> > >> 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<tianjinggenomics.org.cn>
> > >> 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 <dsteplightgmail.com>
> > >> >>
> > >> >> 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.seu217gmail.com
> > >
> > >> >> 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=JingTian.seu217gmail.com
> > >>
> > >
> > >
> > >
> > > --
> > > 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:tianjing217hotmail.com <MSN%3Atianjing217hotmail.com> <
> MSN%3Atianjing217hotmail.com <MSN%253Atianjing217hotmail.com>>
>

--
-----------------------------
Johnny Withers
601.209.4985
johnnypixelated.net

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]