Hi,
A simple left join on two big table took 5 mins to
finish.
Here is the "explain"
mysql> explain select count(*) from newSequence s left
join newSequence_Homolog h on s.Seq_ID = h.Seq_ID;
+-------+--------+---------------+---------+---------+----------+---------+-------------+
| table | type | possible_keys | key | key_len |
ref | rows | Extra |
+-------+--------+---------------+---------+---------+----------+---------+-------------+
| s | index | NULL | PRIMARY | 50 |
NULL | 2684094 | Using index |
| h | eq_ref | PRIMARY | PRIMARY | 50 |
s.Seq_ID | 1 | Using index |
+-------+--------+---------------+---------+---------+----------+---------+-------------+
2 rows in set (0.00 sec)
here are the two tables' definitaion
mysql> describe newSequence;
+-------------+--------------------------------------------------------------------------------------------------------+------+-----+------------+-------+
| Field | Type
| Null | Key | Default | Extra |
+-------------+--------------------------------------------------------------------------------------------------------+------+-----+------------+-------+
| Seq_ID | varchar(50)
| | PRI | | |
| GenBank_Acc | varchar(10)
| YES | MUL | NULL | |
| Organism | varchar(50)
| | MUL | | |
| Seq_Type | enum('EST','GSS','EST Contig','EST
Singlet','GSS Contig','GSS Singlet','GSS Plasmid
Contig','Protein') | | MUL | EST | |
| Seq_Length | int(11)
| | | 0 | |
| Seq_Title | text
| | MUL | | |
| Comment | text
| YES | MUL | NULL | |
| Entry_Date | date
| | | 0000-00-00 | |
+-------------+--------------------------------------------------------------------------------------------------------+------+-----+------------+-------+
8 rows in set (0.00 sec)
There are 2684094 records on this table.
mysql> describe newSequence_Homolog;
+------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key |
Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| Seq_ID | varchar(50) | | PRI |
| |
| Homolog1_PID | varchar(20) | YES | MUL | NULL
| |
| Homolog1_Desc | varchar(50) | YES | MUL | NULL
| |
| Homolog1_Species | varchar(50) | YES | | NULL
| |
| Homolog2_PID | varchar(20) | YES | MUL | NULL
| |
| Homolog2_Desc | varchar(50) | YES | MUL | NULL
| |
| Homolog2_Species | varchar(50) | YES | | NULL
| |
| Homolog3_PID | varchar(20) | YES | MUL | NULL
| |
| Homolog3_Desc | varchar(50) | YES | MUL | NULL
| |
| Homolog3_Species | varchar(50) | YES | | NULL
| |
+------------------+-------------+------+-----+---------+-------+
10 rows in set (0.00 sec)
There are 357944 records in this tables.
I've already copied
/usr/share/doc/mysql-server-3.23.49/my-huge.cnf as
/etc/my.cnf
Is there any other thing I can do to improve the speed
of join? I really hate to merge the two tables
together. I am running MySQL3.23.49 on redhat
linux7.3. My MySQL Server has 4 GB memory.
Eventually, I need to do (select *) instead of the
above select count(*)
mysql> explain select * from newSequence s left join
newSequence_Homolog h on s.Seq_ID = h.Seq_ID;
+-------+--------+---------------+---------+---------+----------+---------+-------+
| table | type | possible_keys | key | key_len |
ref | rows | Extra |
+-------+--------+---------------+---------+---------+----------+---------+-------+
| s | ALL | NULL | NULL | NULL |
NULL | 2684094 | |
| h | eq_ref | PRIMARY | PRIMARY | 50 |
s.Seq_ID | 1 | |
+-------+--------+---------------+---------+---------+----------+---------+-------+
2 rows in set (0.00 sec)
Thanks!
Qunfeng Dong
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
---------------------------------------------------------------------
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
