Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2011
    Posts
    18

    Lightbulb Unanswered: Super Slow Query..

    I have a table with every position we know for each chromosome

    Table 1. Reference of known position - Comparison of the cells together
    +------+--------+------+------+------+
    | chr | pos | cell1 | cell2| cell3|
    +------+--------+------+------+------+
    | 2 | 21662 | NULL | NULL | NULL |
    | 2 | 21693 | NULL | NULL | NULL |
    | 2 | 24250 | NULL | NULL | NULL |
    | 2 | 24445 | NULL | NULL | NULL |
    | 2 | 24554 | NULL | NULL | NULL |
    | 2 | 24800 | NULL | NULL | NULL |
    | 2 | 25252 | NULL | NULL | NULL |
    | 2 | 25279 | NULL | NULL | NULL |
    | 2 | 26634 | NULL | NULL | NULL |
    | 2 | 27286 | NULL | NULL | NULL |
    | 2 | 27373 | NULL | NULL | NULL |
    | 2 | 27793 | NULL | NULL | NULL |
    | 2 | 28721 | NULL | NULL | NULL |
    | 2 | 30291 | NULL | NULL | NULL |


    Table2. Position of cell1 to compare with table1
    +------+--------+------+------+
    | chr | pos | cell1 | type |
    +------+--------+------+------+
    | 2 | 21662 | NULL | heterozygote |
    | 2 | 21693 | NULL | homozygote |
    | 2 | 24250 | NULL | heterozygote |
    | 2 | 24445 | NULL | heterozygote |
    | 2 | 24554 | NULL | homozygote |
    | 2 | 27793 | NULL | homozygote |
    | 2 | 28721 | NULL | heterozygote |

    GOAL : Everytime that a CHR and POS match from table2 (cell1) to table 1 (reference position), if we have :
    - type = heterozygote --> write 1 in table1.cell1
    - type = homozygote --> write 2 in table1.cell1

    In order to have something like that :

    EXPECTED RESULT:
    +------+--------+------+------+------+
    | chr | pos | cell1 | cell2| cell3|
    +------+--------+------+------+------+
    | 2 | 21662 | 1 | NULL | NULL |
    | 2 | 21693 | 2 | NULL | NULL |
    | 2 | 24250 | 1 | NULL | NULL |
    | 2 | 24445 | 1 | NULL | NULL |
    | 2 | 24554 | 2 | NULL | NULL |
    | 2 | 24800 | NULL | NULL | NULL |
    | 2 | 25252 | NULL | NULL | NULL |
    | 2 | 25279 | NULL | NULL | NULL |
    | 2 | 26634 | NULL | NULL | NULL |
    | 2 | 27286 | NULL | NULL | NULL |
    | 2 | 27373 | NULL | NULL | NULL |
    | 2 | 27793 | 2 | NULL | NULL |
    | 2 | 28721 | 1 | NULL | NULL |
    | 2 | 30291 | NULL | NULL | NULL |

    So here is what I did :
    Code:
    UPDATE tbl1 CROSS JOIN tbl2 ON
    (tbl1.pos = tbl2 AND tbl2.type='heterozygote')
    SET tbl1.cell1='1';
    
    UPDATE tbl1 CROSS JOIN tbl2 ON
    (tbl1.pos = tbl2 AND tbl2.type='homozygote')
    SET tbl1.cell1='2';
    There's only one match possible between tbl2 and tbl1, so maybe a cross join wasn't a good idea from the beginning..
    My tables are over 300MB each query takes hours to run..
    Im looking for a faster solution, any suggestions are welcome ~
    Last edited by madkitty; 06-07-11 at 06:34.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what indexes do you have on the tables? the easiest way to show us would be to do a SHOW CREATE TABLE command for each table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2011
    Posts
    18
    Here is a show create table of tbl1 and tbl2. Rows I need to update or compare are in bold
    mysql> show create table tbl1;
    +-------+----------------------------------------
    --------------------------------+
    | Table | Create Table
    |
    +-------+----------------------------------------
    --------------------------------+
    | tbl1 | CREATE TABLE `tbl1` (
    `chr` char(1) DEFAULT NULL,
    `pos` int(11) DEFAULT NULL,

    `cell1` smallint(6) DEFAULT NULL,
    `cell2` smallint(6) DEFAULT NULL,
    `cell3` smallint(6) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
    +-------+----------------------------------------
    --------------------------------+
    1 row in set (0.05 sec)

    mysql> show create table tbl2;
    +-------+----------------------------------------
    -------------------------------------------------
    -------------------------------------------------
    | Table | Create Table


    +-------+----------------------------------------
    -------------------------------------------------
    -------------------------------------------------
    | tbl2 | CREATE TABLE `tbl2` (
    `chr` int(11) DEFAULT NULL,
    `pos` int(11) DEFAULT NULL,

    `ref` char(1) DEFAULT NULL,
    `allele` char(1) DEFAULT NULL,
    `unique_A` char(1) DEFAULT NULL,
    `unique_C` char(1) DEFAULT NULL,
    `unique_G` char(1) DEFAULT NULL,
    `unique_T` char(1) DEFAULT NULL,
    `allele_count` int(11) DEFAULT NULL,
    `other_count` int(11) DEFAULT NULL,
    `pct` decimal(9,4) DEFAULT NULL,
    `type` varchar(255) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
    +-------+----------------------------------------
    -------------------------------------------------
    -------------------------------------------------
    1 row in set (0.00 sec)
    Last edited by madkitty; 06-07-11 at 11:14.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by madkitty View Post
    ... each query takes hours to run..
    Im looking for a faster solution, any suggestions are welcome ~
    i'd say you need some indexes -- you don't gots any
    Code:
    ALTER TABLE tbl1 ADD INDEX one_two_1 ( chr , pos ) ;
    
    ALTER TABLE tbl2 ADD INDEX one_two_2 ( chr , pos ) ;
    now try a single update query like this --
    Code:
    UPDATE tbl1 
    INNER 
      JOIN tbl2 
        ON tbl2.chr = tbl1.chr
       AND tbl2.pos = tbl1.pos
       SET tbl1.cell1 = 
           CASE WHEN tbl2.type = 'heterozygote' THEN 1
                WHEN tbl2.type = 'homozygote'   THEN 2
                ELSE tbl1.cell1 END
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2011
    Posts
    18
    uhm... I luv you Works like a charm Thks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •