MySQL：在CakePHP中Tree行为中对表中字段求和的最快方法

Question

I have table users with tree behavior

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `lft` int(11) DEFAULT NULL,
  `rght` int(11) DEFAULT NULL,
  `user_email` varchar(255) NOT NULL DEFAULT '',
  `user_password` char(100) NOT NULL DEFAULT '',
  `user_name` varchar(255) NOT NULL DEFAULT ''
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and table trades

CREATE TABLE `trades` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `requests` float DEFAULT NULL,
  `trade_date_start` datetime DEFAULT NULL,
  `trade_date_stop` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

and now I need to count sum of all 'requests' (each user can have more than hundreds of 'trades' records, new record is generated once per 4 hours) per user within my tree so I will get (sum of requests in parenthesis) and this is count in scope of one month.

me-
  |
  |_ John (20)
  |  |
  |  |_ John2 (200)
  |  |_ Jane (233)
  |  |_ George (3233)
  |
  |_ Alena (500)
...

So each month I need to see sum of request for each user from 1st day of the current month til last day of current month. It has to be as fast as possible. COuld somebody help me? Thank you

Answer 1

This problem begs for a solution via recursive query, which MySQL alone among commonly used DBMSs does not support. As a result, I think you'll need to perform the necessary recursion yourself if you're tied to MySQL. Each query might have this form:

SELECT
  user_id,
  SUM(requests) AS requests,
FROM
  users
  JOIN trades
    ON users.id = trades.user_id
WHERE
  users.parent_id = <PARENT_ID>
  AND trade_date_start BETWEEN <WINDOW_START_TIMESTAMP> AND <WINDOW_END_TIMESTAMP>
GROUP BY user_id

You would need to process each result row and recursively issue the same sort of query for each user_id returned.

Since you'll be issuing possibly many queries with the same date filtering condition, you might speed it up by first creating a temporary table containing just those rows from trades that fall in the window of interest, then using that (with no explicit date condition) instead of table trades.