Background

We have two tables, one stores school information, the others stores members belongs to that school.
In the school table, there is one column, which stands for the number of members belongs to that school, identically.

Question

We need to find a way to calculate 'How many members are there for a selected school'

Resolution: Use stored procedure

We can calculate the number-of-members periodically from the server side, so that client can query the value directly without the need to calculate it locally.

• We can use a stored procedure to calculate the count for each school from the database side, and below is one working example:

BEGIN
    DECLARE tschool_id VARCHAR(64);
    DECLARE flag int DEFAULT 0;
    DECLARE mem_counter int DEFAULT 0;
    DECLARE curl1 CURSOR FOR SELECT id FROM member_school;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=1;
    open curl1; 
    REPEAT

    FETCH curl1 INTO tschool_id;
    SELECT COUNT(DISTINCT member_id) into mem_counter
        FROM `member_education`  where school_id = tschool_id;
    SET @sql1 = CONCAT('UPDATE member_school set count_num = ', mem_counter, ' where id =',  tschool_id);
    SELECT @sql1;
    PREPARE pre1 FROM @sql1;
    EXECUTE pre1;

    until flag =1 end repeat;
    CLOSE curl1;
END

Review of the effect

For a table contains #616 schools, the procedure takes ~29 seconds to finish the task, which is not within our expectation.
So we change to use Spring scheduled-task to update the records one by one, in an interval.