List:General Discussion« Previous MessageNext Message »
From:Qunfeng Dong Date:December 19 2002 11:28pm
Subject:Thanks! Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too...
View as plain text  
Well, thanks to all of your great help! I am able to
speed up the query {select count(*) from NEW_Sequence
s left join NEW_Sequence_Homolog h on s.Seq_ID =
h.Seq_ID;} from 1 min 52.61 sec down to 20.62 sec now.
The only thing I changed so far was the Seq_ID from
type varchar to bigint. The Seq_ID was not all
numerical for different type of Sequences; but I
managed to assign numerical code to those
non-numerical ones now. 

Qunfeng

> > CREATE TABLE NewSequence
> > (
> >         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     (Seq_ID),
> >         UNIQUE  (GenBank_Acc),
> >         INDEX (Seq_Type),
> >         INDEX (Organism)
> > );
> >
> > This NewSequence table is used to track some
> general
> > info about sequence. Notice I have to use text
> > datatype to describe "Comment" and "Seq_Title"
> fields;
> > therefore I have to use varchar for other string
> > fields. In addition, the Seq_ID is not numerical.
> > BTW, I found indexing on Seq_Type. Organism which
> are
> > very repeative still helps with accessing. This
> table
> > has 2676711 rows.
> >
> >
> > CREATE TABLE NewSequence_Homolog
> > (
> >         Seq_ID          varchar(50)     NOT NULL,
> >         Homolog_PID     int             NOT NULL,
> >         Homolog_Desc    varchar(50)     NOT NULL,
> >         Homolog_Species varchar(50),
> >         PRIMARY KEY     (Seq_ID, Homolog_PID)
> > );
> >
> > This NewSequence_Homolog table is to track which
> > protein sequences (homolog) are similar to the
> > sequence I store in the NewSequence table. This
> table
> > has 997654 rows.
> >
> > mysql> select count(*) from NewSequence s left
> join
> > NewSequence_Homolog h on s.Seq_ID = h.Seq_ID;
> > +----------+
> > | count(*) |
> > +----------+
> > |  3292029 |
> > +----------+
> > 1 row in set (1 min 30.50 sec)
> >
> > So a simple left join took about 1 min and half.
> > First, is this slow or I am too picky?
> >
> > This 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     | 2676711 | Using index |
> > | h     | ref   | PRIMARY       | PRIMARY |     
> 50 |
> > s.Seq_ID |    9976 | Using index |
> >
>
+-------+-------+---------------+---------+---------+----------+---------+--
> -----------+
> >
> >
> > I am running MySQL 3.23.49 on RedHat linux 7.3 on
> a
> > dedicated server with 4 GB memory. The only
> setting I
> > changed is to copy the my-huge.cnf into
> /etc/my.cnf.
> >
> > Qunfeng
> >
> > --- "Michael T. Babcock" <mbabcock@stripped>
> > wrote:
> > > Qunfeng Dong wrote:
> > >
> > > >not-so-good performance (join on tables much
> > > smaller
> > > >than yours takes minutes even using index) and
> I
> > > seem
> > > >to read all the docs I could find on the web
> about
> > > how
> > > >to optimize but they are not working for me (I
> am
> > > >
> > >
> > > Have you stored a slow query log to run them
> through
> > > 'explain' and see
> > > why they're slow?  Do you want to post some of
> them
> > > here so we can
> > > suggest what might be done to make them faster?
> 
=== message truncated ===


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
Thread
Can MySQL handle 120 million records?B.G. Mahesh18 Dec
  • Re: Can MySQL handle 120 million records?Jeremy Zawodny18 Dec
  • Re: Can MySQL handle 120 million records?Qunfeng Dong18 Dec
    • Re: Can MySQL handle 120 million records?Jeremy Zawodny18 Dec
    • Re: Can MySQL handle 120 million records?Michael She18 Dec
      • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
        • RE: Can MySQL handle 120 million records?Adam Nelson18 Dec
      • Re: Can MySQL handle 120 million records?Jeremy Zawodny18 Dec
        • RE: Can MySQL handle 120 million records?Joe Stump18 Dec
          • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
          • Re: Can MySQL handle 120 million records?Gerald Jensen18 Dec
            • Re: Can MySQL handle 120 million records?Aaron Clausen18 Dec
    • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
      • Re: Can MySQL handle 120 million records?Paul DuBois18 Dec
        • Database type questionRichard E. Perlotto II19 Dec
          • Re: Database type questionIikka Meriläinen19 Dec
      • Re[2]: Can MySQL handle 120 million records?Dyego Souza do Carmo19 Dec
    • RE: Can MySQL handle 120 million records?Wayne Lewis18 Dec
      • Excluding records?Eric Anderson19 Dec
        • Re: Excluding records?Adolfo Bello20 Dec
    • Re: Re[2]: Can MySQL handle 120 million records?Harald Fuchs19 Dec
      • Re: Re[2]: Can MySQL handle 120 million records?Paul DuBois19 Dec
      • Re: Re[2]: Can MySQL handle 120 million records?Paul DuBois19 Dec
  • Re: Can MySQL handle 120 million records?Muruganandam18 Dec
    • Re: Can MySQL handle 120 million records?Jeremy Zawodny18 Dec
      • Re: Can MySQL handle 120 million records?Michael She18 Dec
        • Re: Can MySQL handle 120 million records?Jeremy Zawodny18 Dec
          • Re: Can MySQL handle 120 million records?Michael She18 Dec
            • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
              • Re: Can MySQL handle 120 million records?Michael She18 Dec
                • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
            • Re: Can MySQL handle 120 million records?Jeremy Zawodny18 Dec
            • Re: Can MySQL handle 120 million records?Michael Bacarella18 Dec
              • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
            • RE: Can MySQL handle 120 million records?Joe Stump18 Dec
              • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
            • Can I build a web page to extract data?Gary Hostetler18 Dec
            • Re: Can MySQL handle 120 million records?Csongor Fagyal19 Dec
          • Re: Can MySQL handle 120 million records?Michael She18 Dec
        • Re: Can MySQL handle 120 million records?Jocelyn Fournier18 Dec
    • Re: Can MySQL handle 120 million records?Jocelyn Fournier18 Dec
      • Re: Can MySQL handle 120 million records?W. D.18 Dec
    • Re: Can MySQL handle 120 million records?Jocelyn Fournier18 Dec
    • Re: Can MySQL handle 120 million records?Csongor Fagyal18 Dec
      • Re: Can MySQL handle 120 million records?W. D.18 Dec
    • Re: Can MySQL handle 120 million records?Csongor Fagyal18 Dec
Re: Can MySQL handle 120 million records?B.G. Mahesh18 Dec
  • Re: Can MySQL handle 120 million records?David T-G18 Dec
    • RE: Can MySQL handle 120 million records?JamesD18 Dec
      • Re: Can MySQL handle 120 million records?David T-G18 Dec
RE: Can MySQL handle 120 million records?Peter Vertes18 Dec
  • RE: Can MySQL handle 120 million records? - Impressive! How do you guys do that?Qunfeng Dong18 Dec
    • Re: Can MySQL handle 120 million records? - Impressive! How doyou guys do that?R. Hannes Niedner18 Dec
    • Re: Can MySQL handle 120 million records? - Impressive! How do youguys do that?Michael T. Babcock18 Dec
      • Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too :-)Qunfeng Dong18 Dec
      • Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too :-)Qunfeng Dong18 Dec
        • Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too :-)Jocelyn Fournier19 Dec
          • Thanks! Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too...Qunfeng Dong20 Dec
        • Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too :-)Ryan Fox19 Dec
        • Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too :-)Brent Baisley19 Dec
  • RE: Can MySQL handle 120 million records?JamesD19 Dec
    • Re: Can MySQL handle 120 million records?Jeremy Zawodny21 Dec
      • Re: Can MySQL handle 120 million records?Michael She21 Dec
        • Re: Can MySQL handle 120 million records?Michael T. Babcock23 Dec
      • Re: Can MySQL handle 120 million records?Michael She21 Dec
      • RE: Can MySQL handle 120 million records?JamesD21 Dec
        • Re: Can MySQL handle 120 million records?Jeremy Zawodny22 Dec
          • Re: Can MySQL handle 120 million records?Michael T. Babcock23 Dec
            • Re: Can MySQL handle 120 million records?Jeremy Zawodny23 Dec
            • DELETE with no WHERE clauseGordon23 Dec
              • Re: DELETE with no WHERE clauseDennis Salguero24 Dec
              • re: DELETE with no WHERE clauseVictoria Reznichenko24 Dec
      • RE: Can MySQL handle 120 million records?JamesD21 Dec
        • RE: Can MySQL handle 120 million records?Dean Harding21 Dec
          • RE: Can MySQL handle 120 million records?JamesD21 Dec
RE: Can MySQL handle 120 million records?David Brodbeck18 Dec
RE: Can MySQL handle 120 million records?Greg_Cope18 Dec
Re: Can MySQL handle 120 million records?Paul DuBois18 Dec
RE: Can MySQL handle 120 million records?RBRoa19 Dec
RE: Can MySQL handle 120 million records?Dana Diederich19 Dec
RE: Can MySQL handle 120 million records?John Griffin19 Dec
Re: Excluding records?King_Henree@yahoo.com20 Dec