mysql select query

--0016364ef3c4bffd75046e8e67fd
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

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

--0016364ef3c4bffd75046e8e67fd--

Re: mysql select query

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: mysql select query

--001636427251955fd8046e8eb0cc
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

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

> 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
> 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@hotmail.com

--001636427251955fd8046e8eb0cc--

Re: mysql select query

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 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=A0 between 30000 and
> 8030000;
>
> +----+-------------+---------+-------+-----------------+---- -------------=
+---------+------+-------+-------------+
> | id | select_type | table   | type=A0 | possible_keys   | key=A0=
          =A0 |
> key_len | ref=A0 | rows=A0 | Extra       |
> +----+-------------+---------+-------+-----------------+---- -------------=
+---------+------+-------+-------------+
> |=A0 1 | SIMPLE    =A0 | REF_SEQ | range | index_seq_start | inde=
x_seq_start |
> 5       | NULL | 90886 | Using where |
> +----+-------------+---------+-------+-----------------+---- -------------=
+---------+------+-------+-------------+
>
> index_seq_start is the index on start_postion,
>
> 2009/7/13 Darryle Steplight
>>
>> 1. Don't use SELECT *. =A0Only 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
>> =A0max_postion" .
>>
>> On Mon, Jul 13, 2009 at 12:03 AM, JingTian
>> wrote:
>> > Hi all,
>> >
>> > i use "select * from table_name where start_postion between min_postio=
n
>> > 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@hotmail.com
>



--=20
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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: mysql select query

--00163683207206a058046e8edd3e
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

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

> 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
> 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
> >>
> >> 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
> >> 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.seu217@gmail.com
>
>


--
Tianjing

--00163683207206a058046e8edd3e--

Re: mysql select query

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 wrote:
> sorry fo that, but i really need all cols in the table, i think the probl=
em
> maybe caused by one of the col which is text type, each record of this co=
l
> has 2000 characters. this makes the size of record more biger.
>
> 2009/7/13 Darryle Steplight
>>
>> 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
>> 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=A0 between 30000 an=
d
>> > 8030000;
>> >
>> >
>> > +----+-------------+---------+-------+-----------------+---- ----------=
---+---------+------+-------+-------------+
>> > | id | select_type | table   | type=A0 | possible_keys   | key
>> > |
>> > key_len | ref=A0 | rows=A0 | Extra       |
>> >
>> > +----+-------------+---------+-------+-----------------+---- ----------=
---+---------+------+-------+-------------+
>> > |=A0 1 | SIMPLE    =A0 | REF_SEQ | range | index_seq_start | i=
ndex_seq_start
>> > |
>> > 5       | NULL | 90886 | Using where |
>> >
>> > +----+-------------+---------+-------+-----------------+---- ----------=
---+---------+------+-------+-------------+
>> >
>> > index_seq_start is the index on start_postion,
>> >
>> > 2009/7/13 Darryle Steplight
>> >>
>> >> 1. Don't use SELECT *. =A0Only 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
>> >> =A0max_postion" .
>> >>
>> >> On Mon, Jul 13, 2009 at 12:03 AM, JingTian
>> >> 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 t=
he
>> >> > 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:
>> =A0http://lists.mysql.com/mysql?unsub=3DJingTian.seu217@gmai l.com
>>
>
>
>
> --
> Tianjing
>
>



--=20
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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: mysql select query

--0016367f981c234794046e8f4107
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

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

> 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
> 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
> >>
> >> 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
> >> 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
> >> >>
> >> >> 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 > >
> >> >> 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.seu217@gmail.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:tianjing217@hotmail.com

--0016367f981c234794046e8f4107--

Re: mysql select query

--000e0cd28a42265ac9046e9687a8
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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 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
>
> > 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
> > 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
> > >>
> > >> 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
> > >> 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
> > >> >>
> > >> >> 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@gmail.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.seu217@gmail.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:tianjing217@hotmail.com <
> MSN%3Atianjing217@hotmail.com >
>



--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--000e0cd28a42265ac9046e9687a8--

Re: mysql select query

--0016364eca9ece1d96046e96d670
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

the REF_SEQ is defined below, the col DNA_SEQ is a string such as
"ATGCGGTTA",

| REF_SEQ | CREATE TABLE `REF_SEQ` (
`SEQ_ID` int(11) NOT NULL auto_increment,
`REF_ID` int(11) NOT NULL,
`START_POSITION` int(11) NOT NULL,
`END_POSITION` int(11) NOT NULL,
`DNA_SEQ` text,
`DNA_QUALITY` text,
PRIMARY KEY (`SEQ_ID`),
KEY `index_ref_start` (`REF_ID`,`START_POSITION`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

i create a index on cols REF_ID and START_POSITION, i also use analyze table
REF_SEQ to optimization the query,
and now the explain output is:


mysql> 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 | ALL | NULL | NULL | NULL | NULL
| 219728 | Using where |
+----+-------------+---------+------+---------------+------+ ---------+------+--------+-------------+




2009/7/13 Johnny Withers

> 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 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
>>
>> > 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
>> > 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
>> > >>
>> > >> 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
>> > >> 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
>> > >> >>
>> > >> >> 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@gmail.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.seu217@gmail.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?
>> >
>>
>>
>>
> --
> -----------------------------
> Johnny Withers
> 601.209.4985
> johnny@pixelated.net
>



--
Tianjing

--0016364eca9ece1d96046e96d670--

Re: mysql select query

--000e0cd304bebe0183046e9713d3
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

I see that index_ref_start is defined on Ref_Id and Start_Position. Mysql
only uses the left-most column of this index. Drop and re-add this key only
defined as

INDEX idx_ref_start(start_position)

and see if that helps.

Your explain you sent this time is not even using the index.

In your previous explain output, mysql said the key_len is 5. Since both
columns in this key are INT (4-bytes), it says it's only using the left-most
column, REF_ID. I'm not sure why it says 5 and not 4, maybe someone else can
explain this.

I'd redefine the index to only use the a single column, then define a new
index on REF_ID if you use that in JOINs.



On Mon, Jul 13, 2009 at 9:07 AM, TianJing wrote:

> the REF_SEQ is defined below, the col DNA_SEQ is a string such as
> "ATGCGGTTA",
>
> | REF_SEQ | CREATE TABLE `REF_SEQ` (
> `SEQ_ID` int(11) NOT NULL auto_increment,
> `REF_ID` int(11) NOT NULL,
> `START_POSITION` int(11) NOT NULL,
> `END_POSITION` int(11) NOT NULL,
> `DNA_SEQ` text,
> `DNA_QUALITY` text,
> PRIMARY KEY (`SEQ_ID`),
> KEY `index_ref_start` (`REF_ID`,`START_POSITION`)
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
>
> i create a index on cols REF_ID and START_POSITION, i also use analyze
> table REF_SEQ to optimization the query,
> and now the explain output is:
>
>
> mysql> 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 | ALL | NULL | NULL | NULL | NULL
> | 219728 | Using where |
>
> +----+-------------+---------+------+---------------+------+ ---------+------+--------+-------------+
>
>
>
>
> 2009/7/13 Johnny Withers
>
>> 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 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
>>>
>>> > 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
>>> > 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
>>> > >>
>>> > >> 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 >>> >
>>> > >> 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
>>> > >> >>
>>> > >> >> 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@gmail.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.seu217@gmail.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?
>>> >
>>>
>>>
>>>
>> --
>> -----------------------------
>> Johnny Withers
>> 601.209.4985
>> johnny@pixelated.net
>>
>
>
>
> --
> Tianjing
>
>


--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--000e0cd304bebe0183046e9713d3--

Re: mysql select query

--00163646c346309cb6046e97607b
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

sorry for my careless,the sql should be select * from REF_SEQ where REF_ID =
3 and START_POSITION between 30000 and 8030000;

the explain output is :


mysql> explain select * from REF_SEQ where REF_ID = 3 and 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_ref_start | index_ref_start |
8 | NULL | 2408 | Using where |
+----+-------------+---------+-------+-----------------+---- -------------+---------+------+------+-------------+

in this sql,the index is on REF_ID and START_POSITION, the rows in the
output is more less than that index_POS on START_POSITION and index_ref on
REF_ID.

2009/7/13 Johnny Withers

> I see that index_ref_start is defined on Ref_Id and Start_Position. Mysql
> only uses the left-most column of this index. Drop and re-add this key only
> defined as
>
> INDEX idx_ref_start(start_position)
>
> and see if that helps.
>
> Your explain you sent this time is not even using the index.
>
> In your previous explain output, mysql said the key_len is 5. Since both
> columns in this key are INT (4-bytes), it says it's only using the
> left-most
> column, REF_ID. I'm not sure why it says 5 and not 4, maybe someone else
> can
> explain this.
>
> I'd redefine the index to only use the a single column, then define a new
> index on REF_ID if you use that in JOINs.
>
>
>
> On Mon, Jul 13, 2009 at 9:07 AM, TianJing
> wrote:
>
> > the REF_SEQ is defined below, the col DNA_SEQ is a string such as
> > "ATGCGGTTA",
> >
> > | REF_SEQ | CREATE TABLE `REF_SEQ` (
> > `SEQ_ID` int(11) NOT NULL auto_increment,
> > `REF_ID` int(11) NOT NULL,
> > `START_POSITION` int(11) NOT NULL,
> > `END_POSITION` int(11) NOT NULL,
> > `DNA_SEQ` text,
> > `DNA_QUALITY` text,
> > PRIMARY KEY (`SEQ_ID`),
> > KEY `index_ref_start` (`REF_ID`,`START_POSITION`)
> > ) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
> >
> > i create a index on cols REF_ID and START_POSITION, i also use analyze
> > table REF_SEQ to optimization the query,
> > and now the explain output is:
> >
> >
> > mysql> 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 | ALL | NULL | NULL | NULL |
> NULL
> > | 219728 | Using where |
> >
> >
> +----+-------------+---------+------+---------------+------+ ---------+------+--------+-------------+
> >
> >
> >
> >
> > 2009/7/13 Johnny Withers
> >
> >> 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 > >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
> >>>
> >>> > 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
> >>> > 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
> >>> > >>
> >>> > >> 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@genomics.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
> >>> > >> >>
> >>> > >> >> 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@gmail.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.seu217@gmail.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?
> >>> >
> >>>
> >>>
> >>>
> >> --
> >> -----------------------------
> >> Johnny Withers
> >> 601.209.4985
> >> johnny@pixelated.net
> >>
> >
> >
> >
> > --
> > Tianjing
> >
> >
>
>
> --
> -----------------------------
> Johnny Withers
> 601.209.4985
> johnny@pixelated.net
>



--
Tianjing

Bioinformatics Center,
Beijing Genomics Institute,Shenzhen
Tel:+86-755-25273851
MSN:tianjing217@hotmail.com

--00163646c346309cb6046e97607b--

Re: mysql select query

--000e0cd2421e1a5660046e98db46
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

It looks like MySQL is using both columns in the key for that query, since
the key_len is 8, but for some reason it says it is still "using where".

What happens when you only select these fields: seq_id, ref_id,
start_position, end_position?

Does the query speed up? I had a table that had some TEXT columns defined
and I found when I selected every column excep the TEXT column the query ran
faster.



On Mon, Jul 13, 2009 at 9:45 AM, TianJing wrote:

> sorry for my careless,the sql should be select * from REF_SEQ where REF_ID
> = 3 and START_POSITION between 30000 and 8030000;
>
> the explain output is :
>
>
> mysql> explain select * from REF_SEQ where REF_ID = 3 and 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_ref_start | index_ref_start |
> 8 | NULL | 2408 | Using where |
>
> +----+-------------+---------+-------+-----------------+---- -------------+---------+------+------+-------------+
>
> in this sql,the index is on REF_ID and START_POSITION, the rows in the
> output is more less than that index_POS on START_POSITION and index_ref on
> REF_ID.
>
>
> 2009/7/13 Johnny Withers
>
>> I see that index_ref_start is defined on Ref_Id and Start_Position. Mysql
>> only uses the left-most column of this index. Drop and re-add this key
>> only
>> defined as
>>
>> INDEX idx_ref_start(start_position)
>>
>> and see if that helps.
>>
>> Your explain you sent this time is not even using the index.
>>
>> In your previous explain output, mysql said the key_len is 5. Since both
>> columns in this key are INT (4-bytes), it says it's only using the
>> left-most
>> column, REF_ID. I'm not sure why it says 5 and not 4, maybe someone else
>> can
>> explain this.
>>
>> I'd redefine the index to only use the a single column, then define a new
>> index on REF_ID if you use that in JOINs.
>>
>>
>>
>> On Mon, Jul 13, 2009 at 9:07 AM, TianJing
>> wrote:
>>
>> > the REF_SEQ is defined below, the col DNA_SEQ is a string such as
>> > "ATGCGGTTA",
>> >
>> > | REF_SEQ | CREATE TABLE `REF_SEQ` (
>> > `SEQ_ID` int(11) NOT NULL auto_increment,
>> > `REF_ID` int(11) NOT NULL,
>> > `START_POSITION` int(11) NOT NULL,
>> > `END_POSITION` int(11) NOT NULL,
>> > `DNA_SEQ` text,
>> > `DNA_QUALITY` text,
>> > PRIMARY KEY (`SEQ_ID`),
>> > KEY `index_ref_start` (`REF_ID`,`START_POSITION`)
>> > ) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
>> >
>> > i create a index on cols REF_ID and START_POSITION, i also use analyze
>> > table REF_SEQ to optimization the query,
>> > and now the explain output is:
>> >
>> >
>> > mysql> 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 | ALL | NULL | NULL | NULL |
>> NULL
>> > | 219728 | Using where |
>> >
>> >
>> +----+-------------+---------+------+---------------+------+ ---------+------+--------+-------------+
>> >
>> >
>> >
>> >
>> > 2009/7/13 Johnny Withers
>> >
>> >> 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 >> >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
>> >>>
>> >>> > 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 >> >
>> >>> > 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
>> >>> > >>
>> >>> > >> 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@genomics.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
>> >>> > >> >>
>> >>> > >> >> 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@gmail.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.seu217@gmail.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?
>> >>> >
>> >>>
>> >>>
>> >>>
>> >> --
>> >> -----------------------------
>> >> Johnny Withers
>> >> 601.209.4985
>> >> johnny@pixelated.net
>> >>
>> >
>> >
>> >
>> > --
>> > Tianjing
>> >
>> >
>>
>>
>> --
>> -----------------------------
>> Johnny Withers
>> 601.209.4985
>> johnny@pixelated.net
>>
>
>
>
> --
> Tianjing
>
> Bioinformatics Center,
> Beijing Genomics Institute,Shenzhen
> Tel:+86-755-25273851
> MSN:tianjing217@hotmail.com
>



--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--000e0cd2421e1a5660046e98db46--

Re: mysql select query

--00163646d92caaaf29046e9ff17e
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

yes,it is more faster that i select every cols except the TEXT col,but
unfortunately i need the TEXT cols for next step.

2009/7/14 Johnny Withers

> It looks like MySQL is using both columns in the key for that query, since
> the key_len is 8, but for some reason it says it is still "using where".
>
> What happens when you only select these fields: seq_id, ref_id,
> start_position, end_position?
>
> Does the query speed up? I had a table that had some TEXT columns defined
> and I found when I selected every column excep the TEXT column the query ran
> faster.
>
>
>
> On Mon, Jul 13, 2009 at 9:45 AM, TianJing wrote:
>
>> sorry for my careless,the sql should be select * from REF_SEQ where REF_ID
>> = 3 and START_POSITION between 30000 and 8030000;
>>
>> the explain output is :
>>
>>
>> mysql> explain select * from REF_SEQ where REF_ID = 3 and 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_ref_start | index_ref_start |
>> 8 | NULL | 2408 | Using where |
>>
>> +----+-------------+---------+-------+-----------------+---- -------------+---------+------+------+-------------+
>>
>> in this sql,the index is on REF_ID and START_POSITION, the rows in the
>> output is more less than that index_POS on START_POSITION and index_ref on
>> REF_ID.
>>
>>
>> 2009/7/13 Johnny Withers
>>
>>> I see that index_ref_start is defined on Ref_Id and Start_Position. Mysql
>>> only uses the left-most column of this index. Drop and re-add this key
>>> only
>>> defined as
>>>
>>> INDEX idx_ref_start(start_position)
>>>
>>> and see if that helps.
>>>
>>> Your explain you sent this time is not even using the index.
>>>
>>> In your previous explain output, mysql said the key_len is 5. Since both
>>> columns in this key are INT (4-bytes), it says it's only using the
>>> left-most
>>> column, REF_ID. I'm not sure why it says 5 and not 4, maybe someone else
>>> can
>>> explain this.
>>>
>>> I'd redefine the index to only use the a single column, then define a new
>>> index on REF_ID if you use that in JOINs.
>>>
>>>
>>>
>>> On Mon, Jul 13, 2009 at 9:07 AM, TianJing
>>> wrote:
>>>
>>> > the REF_SEQ is defined below, the col DNA_SEQ is a string such as
>>> > "ATGCGGTTA",
>>> >
>>> > | REF_SEQ | CREATE TABLE `REF_SEQ` (
>>> > `SEQ_ID` int(11) NOT NULL auto_increment,
>>> > `REF_ID` int(11) NOT NULL,
>>> > `START_POSITION` int(11) NOT NULL,
>>> > `END_POSITION` int(11) NOT NULL,
>>> > `DNA_SEQ` text,
>>> > `DNA_QUALITY` text,
>>> > PRIMARY KEY (`SEQ_ID`),
>>> > KEY `index_ref_start` (`REF_ID`,`START_POSITION`)
>>> > ) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
>>> >
>>> > i create a index on cols REF_ID and START_POSITION, i also use analyze
>>> > table REF_SEQ to optimization the query,
>>> > and now the explain output is:
>>> >
>>> >
>>> > mysql> 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 | ALL | NULL | NULL | NULL |
>>> NULL
>>> > | 219728 | Using where |
>>> >
>>> >
>>> +----+-------------+---------+------+---------------+------+ ---------+------+--------+-------------+
>>> >
>>> >
>>> >
>>> >
>>> > 2009/7/13 Johnny Withers
>>> >
>>> >> 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 >>> >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
>>> >>>
>>> >>> > 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@genomics.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
>>> >>> > >>
>>> >>> > >> 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@genomics.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
>>> >>> > >> >>
>>> >>> > >> >> 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@gmail.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.seu217@gmail.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?
>>> >>> >
>>> >>>
>>> >>>
>>> >>>
>>> >> --
>>> >> -----------------------------
>>> >> Johnny Withers
>>> >> 601.209.4985
>>> >> johnny@pixelated.net
>>> >>
>>> >
>>> >
>>> >
>>> > --
>>> > Tianjing
>>> >
>>> >
>>>
>>>
>>> --
>>> -----------------------------
>>> Johnny Withers
>>> 601.209.4985
>>> johnny@pixelated.net
>>>
>>
>>
>>
>> --
>> Tianjing
>>
>> Bioinformatics Center,
>> Beijing Genomics Institute,Shenzhen
>> Tel:+86-755-25273851
>> MSN:tianjing217@hotmail.com
>>
>
>
>
> --
> -----------------------------
> Johnny Withers
> 601.209.4985
> johnny@pixelated.net
>



--
Tianjing

Bioinformatics Center,
Beijing Genomics Institute,Shenzhen
Tel:+86-755-25273851
MSN:tianjing217@hotmail.com

--00163646d92caaaf29046e9ff17e--