List:General Discussion« Previous MessageNext Message »
From:Jocelyn Fournier Date:December 19 2002 1:02am
Subject: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  
Hi,

I think you'd better add an unique ID to both table defined as int
corresponding to each seq_ID, and then do the join on this ID rather than on
Seq_ID (join on varchar is far from the fastest solution :)) (unless seq_ID
could be converted into int directly ?)

(but it takes time, even for me (bi athlon MP 2200+) :


mysql> SELECT COUNT(*) FROM searchmainhardwarefr7 LEFT JOIN
searchjoinhardwarefr7 ON
searchjoinhardwarefr7.numreponse=searchmainhardwarefr7.numreponse;
+----------+
| COUNT(*) |
+----------+
| 39396361 |
+----------+
1 row in set (3 min 23.15 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM searchmainhardwarefr7 LEFT JOIN
searchjoinhardwarefr7 ON
searchjoinhardwarefr7.numreponse=searchmainhardwarefr7.numreponse;
+----+-------------+-----------------------+--------+---------------+-------
-----+---------+----------------------------------+----------+-------------+
| id | select_type | table                 | type   | possible_keys | key
| key_len | ref                              | rows     | Extra       |
+----+-------------+-----------------------+--------+---------------+-------
-----+---------+----------------------------------+----------+-------------+
|  1 | SIMPLE      | searchmainhardwarefr7 | index  | NULL          |
numreponse |       4 | NULL                             | 39396576 | Using
index |
|  1 | SIMPLE      | searchjoinhardwarefr7 | eq_ref | numreponse    |
numreponse |       4 | searchmainhardwarefr7.numreponse |        1 | Using
index |
+----+-------------+-----------------------+--------+---------------+-------
-----+---------+----------------------------------+----------+-------------+
)


Regards,
  Jocelyn
----- Original Message -----
From: "Qunfeng Dong" <dongqunfeng@stripped>
To: <mysql@stripped>
Sent: Wednesday, December 18, 2002 9:17 PM
Subject: 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
:-)


> Boy, you guys are die-hard MySQL fans :-) I think your
> strong defending convinced us MySQL can handle 120
> million records :-) But I know some ordinary users out
> there like me who are not experts on tuning the MySQL
> performance (they did send me private emails saying
> they encountered the similar slow join problem). So
> please help us to keep the faith.
>
> We are trying to develop a simple biology database to
> maintain some DNA Sequence information. My problem is
> coming from the following two tables:
>
> 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?
> >
> > --
> > Michael T. Babcock
> > C.T.O., FibreSpeed Ltd.
> > http://www.fibrespeed.net/~mbabcock
> >
> >
>
>
> __________________________________________________
> 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 <mysql-thread127886@stripped>
> To unsubscribe, e-mail
<mysql-unsubscribe-joc=presence-pc.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
>
>

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