<pre class="c" name="code">先建立原本的tables

CREATE TABLE main_table (
mt_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
mt_label VARCHAR(20)
) ENGINE=InnoDB;

CREATE TABLE hits_table (
ht_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
ht_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
ht_ip VARCHAR(19) NOT NULL,
mt_id INT NOT NULL,
CONSTRAINT `FK__main_table` FOREIGN KEY (`mt_id`) REFERENCES `main_table` (`mt_id`)
) ENGINE=InnoDB;

--------------------------
插入一些測試資料

INSERT INTO main_table VALUES
(1,'test1'),(2,'test2'),(3,'test3'),(4,'test4');

INSERT INTO hits_table(ht_ip,mt_id) VALUES
('192.168.1.1',1),
('192.168.1.2',1),
('192.168.1.3',1),
('192.168.2.1',2),
('192.168.2.2',2),
('192.168.4.2',4),
('192.168.4.1',4),
('192.168.4.4',4),
('192.168.4.3',4);

原本撈資料的 
SELECT mt_label, COUNT(ht_id) AS totalHits 
FROM main_table LEFT JOIN hits_table 
ON main_table.mt_id = hits_table.mt_id 
GROUP BY hits_table.mt_id 
ORDER BY COUNT(ht_id) DESC;

+----------+-----------+
| mt_label | totalHits |
+----------+-----------+
| test4    |         4 |
| test1    |         3 |
| test2    |         2 |
| test3    |         0 |
+----------+-----------+
*******************************************
現在想要在main_table中增加一個欄位叫mt_hits,存放hits_table裡面的總和資料.

ALTER TABLE main_table ADD COLUMN mt_hits INT;

現在Main_Table的情形如下:
SELECT * FROM main_table;
+-------+----------+---------+
| mt_id | mt_label | mt_hits |
+-------+----------+---------+
|     1 | test1    |    NULL |
|     2 | test2    |    NULL |
|     3 | test3    |    NULL |
|     4 | test4    |    NULL |
+-------+----------+---------+

樓主是想利用mt_hits來存放,但是現在需要初始化,所以用php包了兩道SQL Commands,
但因資料量大,所以跑很久,故上網求助.
----------------------------------------
我們採用以下語法:

UPDATE main_table a
LEFT JOIN (SELECT mt_id, COUNT(ht_id) AS hits
                  FROM hits_table
                  GROUP BY mt_id) b
ON a.mt_id = b.mt_id
SET a.mt_hits = b.hits;

執行結果:
Rows matched: 4  Changed: 3  Warnings: 0

查驗結果:
>SELECT * FROM main_table;
+-------+----------+---------+
| mt_id | mt_label | mt_hits |
+-------+----------+---------+
|     1 | test1    |       3 |
|     2 | test2    |       2 |
|     3 | test3    |    NULL |
|     4 | test4    |       4 |
+-------+----------+---------+
4 rows in set (0.00 sec)

結果正確! 這樣就能先作此表的新增欄位初始化.
以後若是有新增時,再增加,這個動作建議採用trigger.
這後面有需要,再討論,先把初始化確認.

你在寫什麼?點越率不是這樣做
不是查的時候去做總匯
是人家點閱時就把該欄位的值加一

SELECT mt_label, COUNT(ht_id) AS totalHits
FROM main_table
LEFT JOIN hits_table ON main_table.mt_id = hits_table.mt_id
GROUP BY hits_table.mt_id
ORDER BY COUNT(ht_id) DESC

改成

SELECT a.mt_label, COUNT(b.mt_id) AS totalHits
FROM main_table a
LEFT JOIN hits_table b ON a.mt_id = b.mt_id
GROUP BY a.mt_id
ORDER BY COUNT(b.mt_id) DESC

記得要加一個mt_id的nonclustered index 做 index full scan ,
CREATE NONCLUSTERED IDNEX IX_HIT_TABLES_MT_ID ON HIT_TABLES(MT_ID);

點閱時呼叫stored procedure
stored procedure對每個日期進行Count+1
要取得今日點閱率、本週點閱率、本月點閱率只需用SQL對每個日期的Count進行群組彙總查詢即可

樓上大大們都提供了很具體的解決方案，在下也分享一下我的想法~ 資料量到三千萬筆這種等級，如果要透過JOIN TABLE再加UPDATE，應該是很恐佈的事情。所以其實Albert大大他說的拉到AP層處理或許也是一種可行方案，當然~我提的是自己想像的。

我的作法是玩程式接力賽:
1.先用SQL語法~SELECT ... OUTFILE ... 將三千萬筆的資料匯出成30個csv檔(這樣單檔也只有一百萬筆的csv檔)，例如:

<pre class="c" name="code">
SELECT * FROM `hits_table`  LIMIT 0 , 1000000  
INTO OUTFILE 'C:/testMe01.csv' FIELDS ESCAPED BY '""' TERMINATED BY ',';
SELECT * FROM `hits_table`  LIMIT 1000001 , 1000000  
INTO OUTFILE 'C:/testMe02.csv' FIELDS ESCAPED BY '""' TERMINATED BY ',';
......
......
SELECT * FROM `hits_table`  LIMIT 29000001 , 1000000  
INTO OUTFILE 'C:/testMe30.csv' FIELDS ESCAPED BY '""' TERMINATED BY ',';

2.寫一個Windows Shell Script(AP端)，批次讀入上一階段產生的testMe01.csv~testMe30.csv...再整理出 hits_table 裡面 mt_hits 欄的加總，輸出 update main_table 的語法到一個文字檔，例如: C:/testMeFinal.sql，最後再匯入這段MySQL語法到資料庫就搞定嘍。

以上是如果真的都沒有辦法中的辦法，畢竟可以一行SQL解決的話，沒有人會想再去多繞這些路，所以還是建議目前問題若解決後，發文版主要改一下網站計數的方式，例如 pantc328 大大講的那個方式，或許就可避免目前的問題發生了~ 希望有幫助~