Select count(*) from A update in B Super Slow
I have a query that takes 2minutes to count from table A and update Table B with the count result.
Everytime that a number in Table_B column Start matches the range in Table_A (readstart/readend) I should update read_count in Table_B.
id | readstart | readend | read_count
1 | 2999997 | 3000097 | 0
2 | 3000097 | 3000197 | 0
3 | 3000497 | 3000597 | 0
4 | 3001597 | 3001697 | 0
5 | 3001897 | 3001997 | 0
6 | 3005397 | 3005497 | 0
7 | 3005997 | 3006097 | 0
8 | 3006397 | 3006497 | 0
9 | 3006797 | 3006897 | 0
10| 3007497 | 3007597 | 0
H开发者_JS百科ere is the table I should update with the count result :
CREATE TABLE `rdc_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`readstart` int(11) DEFAULT NULL,
`readend` int(11) DEFAULT NULL,
`read_count` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `readstart` (`readstart`),
KEY `readend` (`readend`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
Here is the table from where I wanna count matching rows :
CREATE TABLE `1ips_chr1` (
`strand` char(1) DEFAULT NULL,
`chr` varchar(10) DEFAULT NULL,
`start` int(11) DEFAULT NULL,
`end` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`name2` varchar(255) DEFAULT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`),
KEY `start` (`start`),
KEY `end` (`end`)
) ENGINE=MyISAM AUTO_INCREMENT=34994289 DEFAULT CHARSET=latin1;
I did a test on 10 rows, the result was horrible..2 minutes to select count(*) and update 10 rows. I have about 350,000 rows in Table_A to update and 35,000,000 in table_B. I know that in average each count should return 30~40 as a result.
Here is my super slow query :
UPDATE rdc_test
SET rdc_test.read_count =
(
SELECT COUNT(start) as read_count
FROM 1ips_chr1
WHERE 1ips_chr1.start >= rdc_test.readstart
AND 1ips_chr1.start <= rdc_test.readend
)
Query OK, 10 rows affected (2 min 22.20 sec)
Rows matched: 10 Changed: 10 Warnings: 0
Try this :
UPDATE rdc_test t1
INNER JOIN
(
SELECT r.id AS id,
COUNT(l.start) AS read_count
FROM rdc_test r
LEFT OUTER JOIN start1ips_chr1 l
ON l.start >= r.readstart
AND l.start <= r.readend
GROUP BY r.id
) t2
ON t1.id = t2.id
SET t1.read_count = t2.read_count
Edit :
Due to the amount of datas you need to update, the best way is to recreate the table instead of perform an update :
CREATE TABLE new_rdc_test AS
SELECT r.id AS id,
r.readstart AS readstart,
r.readend AS readend,
COUNT(l.start) AS read_count
FROM rdc_test r
LEFT OUTER JOIN start1ips_chr1 l
ON l.start >= r.readstart
AND l.start <= r.readend
GROUP BY r.id, r.readstart, r.readend
Does this query run fast enough ?
Try to bring the COUNT(*)
to application level (ie. store it as a variable in PHP/Java) then do the UPDATE
, with that value. MySQL will not have to calculate that count for every record you update.
精彩评论