使用join和group by优化更新查询

I have a table totals with fields DATASOURCE, PEOPLEID, TOTVALUE (~6 milion of records) and a table source1 with field PEOPLEID and VALUE (ab. 3 milion of records). In totals I have peopleids unique for each DATASOURCE, while in source1 I have several records for each peopleid. All peopleids in source1 are already included in totals, but not their values.

我有一个表总计,字段为DATASOURCE,PEOPLEID,TOTVALUE(约6百万条记录)和一个表源1,字段为PEOPLEID和VALUE(约为3百万条记录)。在总计中,我有每个DATASOURCE唯一的人员,而在source1中我有每个人员的几个记录。 source1中的所有personid都已包含在总计中,但不包含在其值中。

I managed to get the update query that update the field TOTVALUE of totals taking it from source1 on a "test" small table, but now that I am trying to get it running on the real table it's running forever... and I'll have to repeat it for source2, source3, and so on...

我设法获得更新查询,更新从“测试”小表上的source1获取的总计字段TOTVALUE,但现在我正试图让它在真实表上运行它永远运行...我会必须为source2,source3等重复它...

The query I run is:

我运行的查询是:

UPDATE  totals t
    INNER JOIN 
    (
        SELECT  peopleid,
                sum(value) AS TotValues
        FROM source1
        GROUP BY peopleid
    ) s
    ON t.peopleid = s.peopleid
SET t.totvalue = s.TotValues
where
t.datasource like 'source1'

Do you think there is a better way to run and optimise this query ?

您认为有更好的方法来运行和优化此查询吗?

2 个解决方案

#1

use = instead of LIKE since, I think, you are not searching patterns right?

use =而不是LIKE因为,我认为,你不是在寻找模式吗?

create an INDEX on column PeopleID on both tables: totals and source

在两个表上的列PeopleID上创建一个INDEX:totals和source

if possible, add also an index on column datasource on table totals

如果可能,在表总计上添加列数据源的索引

query,

UPDATE  totals t
        INNER JOIN 
        (
            SELECT  peopleid,
                    sum(value) AS TotValues
            FROM    source1
            GROUP   BY peopleid
        ) s
        ON t.peopleid = s.peopleid
SET     t.peopleid = s.peopleid
WHERE   t.datasource = 'source1'

#2

Try joining data first. Remember to create indexes for peopleid column in both tables.

尝试先加入数据。请记住在两个表中为peopleid列创建索引。

UPDATE  totals t
    INNER JOIN 
    (
        select * from (
          SELECT  t.datasource,
                  peopleid,
                  sum(value) AS TotValues
          FROM source1 s INNER JOIN 
               totals t
                      ON t.peopleid = s.peopleid
          GROUP BY t.datasource, t.peopleid
        ) s_aux
    ) s
    ON t.peopleid = s.peopleid and t.datasource = s.datasource
SET t.totvalue = s.TotValues

#1

use = instead of LIKE since, I think, you are not searching patterns right?