Gfyuki

To state the problem, this is what I have in the MariaDB/MySQL.

I have the follow table structure, scores

| student_id | x1 | x2 | x3 | y1 | y2 | z1 | z2 | z3 | z4 |

| ---------- | -- | -- | -- | -- | -- | -- | -- | -- | -- |

| 1          | 5  | 3  | 1  | 4  | 3  | 3  | 4  | 1  | 2  |

| 2          | 5  | 3  | 3  | 4  | 2  | 1  | 5  | 2  | 3  |

| 3          | 4  | 2  | 2  | 1  | 1  | 3  | 4  | 3  | 4  |

| 4          | 1  | 4  | 5  | 4  | 5  | 3  | 5  | 5  | 4  |

student_id is the PRIMARY_KEY. Other columns x1, x2... is TINYINT(1) range from 1..5 (inclusive).

The goal:

• To calculate score of one given student_id against a given list of student_ids.


• Result set should have two columns: student_id (exclude the given one), and final_score. It must be sorted by final_score DESC.

The formula to calculate final_score of student A against student B.

• Given: two records of student A and B with list of scores in different categories. For example: category X that have 3 questions, category Y has 2 questions, category Z has 4 questions.


• 
  

  First, calculate the average score in each category first.

  
  
  

  AVG_X = ( ABS(XA1 - XB1) + ABS(XA2 - XB2) + ABS(XA3 - XB3) ) / 3

  
  
  

  AVG_Y = ( ABS(YA1 - YB1) + ABS(YA2 - YB2) ) / 2

  
  
  

  AVG_Z = ( ABS(ZA1 - ZB1) + ABS(ZA2 - ZB2) + ABS(ZA3 - ZB3) + ABS(ZA4 - ZB4) ) / 4

  
  

where as: AVG is the average value of a category. ABS is to get absolute value.

• 
  

  Last, the final score is calculated by:

  
  
  

  FINAL_SCORE = 5 - ((AVG_X + AVG_Y + AVG_Z) / 3)

  
  

Based on this, I've made the following SQL query.

SELECT 

    f.student_id, 

    5 - ( avg_cate_x + avg_cate_y + avg_cate_z ) / 3 as final_score

FROM

(

    SELECT

        s.student_id,

        (

            ABS(s.x1 - u.x1) + ABS(s.x2 - u.x2) + ABS(s.x3 - u.x3)

        ) / 3 AS avg_cate_x,

        (

            ABS(s.y1 - u.y1) + ABS(s.y2 - u.y2)

        ) / 2 AS avg_cate_y,

        (

            ABS(s.z1 - u.z1) + ABS(s.z2 - u.z2) + ABS(s.z3 - u.z3) + ABS(s.z4 - u.z4)

        ) / 4 AS avg_cate_z,



    FROM scores AS s



    JOIN

    ( SELECT * FROM scores WHERE scores.student_id = 1 ) AS u

) AS f



ORDER by final_score DESC;

The performance is very slow when I execute it to get final score of student_id = 1 against the rest of table with 50k records, it takes 970ms.

This is the EXPLAIN

+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------------+

| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra                 |

+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------------+

|  1 | SIMPLE      | scores | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using filesort        |

|  1 | SIMPLE      | s      | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL  |    4 |   100.00 | Using index condition |

+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------------+

I actually posted the question here to get supports on SQL query optimization.

However, I'm wondering if there is any fast aggregation framework that can help me to achieve this.

I tried this with MongoDB using MapReduce but it doesn't seem to fit the requirements. MongoDB processes very slow on MapReduce, and it requires to store the final results into another collections for reading next time. In term of space/disk, the cost is high because I will need to generate a result column for each user.

To re-start the question, I would like to aggregate the table and output a list of values via request, you can read how it works in above sections.

Any guide, suggestion and recommendation is really appreciate.
Thank you :)