Super Slow Query..

madkitty · #1 (**permalink**) 06-07-11, 04:51

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 ~

r937 · #2 (**permalink**) 06-07-11, 06:16

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

madkitty · #3 (**permalink**) 06-07-11, 07:34

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)

r937 · #4 (**permalink**) 06-07-11, 17:35

Quote:

Originally Posted by madkitty

... 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

madkitty · #5 (**permalink**) 06-07-11, 22:55

uhm... I luv you

Works like a charm Thks

Internet Services

Web Development

Digital Marketing

Consumer Tech