----- Original Message -----
From: "Qunfeng Dong" <[EMAIL PROTECTED]>
>
> We are trying to develop a simple biology database to
> maintain some DNA Sequence information. My problem is
> coming from the following two tables:
<snip>
Making indexes smaller will help. Does it need to be varchar(50)?
Also, I'd consider creating a numeric auto_increment primary key on your
NewSequence table, and using it to relate the 2 tables together. It may
make for some more complex SQL statements to describe the relationship, but
you'll gain the time back in performance. Consider using the following.
CREATE TABLE NewSequence
(
id int(11) not null auto_increment,
Seq_ID varchar(50) NOT NULL,
GenBank_Acc varchar(10),
Organism varchar(50) NOT NULL,
Seq_Type enum("EST","GSS","EST Contig","EST
Singlet","GSS Contig","GSS Singlet","GSS Plasmid
Contig","Protein") NOT NULL,
Seq_Length int NOT NULL,
Seq_Title text NOT NULL,
Comment text,
Entry_Date date NOT NULL,
PRIMARY KEY (id),
UNIQUE (Seq_ID),
UNIQUE (GenBank_Acc),
INDEX (Seq_Type),
INDEX (Organism)
);
CREATE TABLE NewSequence_Homolog
(
id int(11) NOT NULL,
Homolog_PID int NOT NULL,
Homolog_Desc varchar(50) NOT NULL,
Homolog_Species varchar(50),
PRIMARY KEY (id, Homolog_PID)
);
This would make your example query:
select count(*) from NewSequence s left join NewSequence_Homolog h on s.id =
h.id;
And this would run much quicker, as instead of searching through 50
character indexes for each table, it would only have to look at 11 digit
indexes. Much quicker.
Ryan
sql, query and stuff
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
