Qunfeng, > A simple left join on two big table took 5 mins to > finish.
These lines tell about the cause of the problem: > | table | type | possible_keys | key | key_len | > | s | index | NULL | PRIMARY | 50 | MySQL has no key (index) which it can use to speed up the search on the first table, newSequence (alias s). So, it has to scan all of the rows: > ref | rows | Extra | > NULL | 2684094 | Using index | MySQL will still use the primary key, _trying_ to be faster than without. Does the Seq_ID have to be VARCHAR? This column type isn't very easy to index, especially without a length specification. As you cannot have CHAR (> 3) and VARCHAR in one table, I would suggest you split up table newSequence into two tables (one fixed-length (i.e. without VARCHAR/TEXT columns), the other variable-length). This will speed up count() queries (and others) amazingly. If you can use something like INT instead of CHAR, it's even faster. If, for any reason, you have to stick to VARCHAR, you should index the column separately. Leave the primary key as is, but add another key (index) like that: CREATE INDEX make_it_fast ON newSequence (Seq_ID(10)); This will only make sense if the first 10 characters can tell the difference between different records. If not, you can experiment setting the index size to 20, 30, ... I hope this will give you some ideas on how you can improve performance. Regards, -- Stefan Hinz <[EMAIL PROTECTED]> CEO / Geschäftsleitung iConnect GmbH <http://iConnect.de> Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 ----- Original Message ----- From: "Qunfeng Dong" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, December 16, 2002 6:42 PM Subject: How can I speed up the Left Join on big tables? > 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 > --------------------------------------------------------------------- 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
