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"
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 TE
sorry for my careless,the sql should be select * from REF_SEQ where REF_ID =
3 and START_POSITION between 3 and 803;
the explain output is :
mysql> explain select * from REF_SEQ where REF_ID = 3 and START_POSITION
between 3 and 803;
++-+-+---+
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 previo
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` tex
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
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
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
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
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,th
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 3 and
803;
++-+-+---+-+-+
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" .
Nanu Kalmanovitz wrote:
Hi!
System is Novell SBS 6.5 sp1 with Apache 2.0.48, MySQL ver. 4.0.15a,
PHP 4.2.3.
We try to define a select query that takes the values of the variables
'varKoshi' and 'varKvish' based on the following 4 tables:
'varKoshi' >>vv<<< 'varKvish'
Vinay,
Wednesday, December 04, 2002, 3:05:03 PM, you wrote:
VM> I m developing a web based application using Tomcat, Struts, JSP, mysql.
VM> I m using mysql 3.23.39-max-nt and mysql-connector-java-2.0.14 to access
VM> mysql through java 1.3.1.
VM> But sometimes the select query on 1 particular t
14 matches
Mail list logo
