MySQL-获取具有最低关联计数的行 [英] Mysql - Get row with lowest relation count

问题描述

我有两个mysql表:

I have two mysql tables:

1. 教师，其列为id, name, age
2. 学生，其列为name, age, teacher_id，其中teacher_id是分配给该学生的老师的ID.

1. Teacher with columns id, name, age
2. Student with columns name, age, teacher_id where teacher_id is the id of the teacher to whom the student is assigned.

现在，教师之间的负担应平均分配.每次将新学生添加到系统中时，我都需要为其分配一个最不忙的老师，即分配给它的学生数量最少.

Now, the load amongst the teachers should be equally distributed. Everytime a new student is added to the system, I need to assign it a teacher who's the least busy i.e. has least number of students assigned to it.

是否有一种非常快速的方法?最初，我以为我可以使用group by teacher_id并按计数对其进行排序以获得它.但是，这排除了尚未分配任何学生的老师.

Is there a really fast way of doing this? Initially, i thought i could use a group by teacher_id and sort it by the count to get it. However, this leaves out teachers that don't have any student assigned to them yet.

如果我们使用联接，那么如果学生表具有100万行，那么性能将如何?

If we're using joins, how will the performance be if the student table has 1million rows?

推荐答案

如果您想要一种真正快速的方法"，那么我建议您重新设计一下.

If you want a "really fast way" of doing this, then I would suggest a small redesign.

实施触发条件可维持所有教师的当前学生人数.例如，这将是一个新列，student_count.

Implement triggers to maintain the current student count for all the teachers. This would be a new column, student_count, say.

在学生人数上添加一个索引，然后简单地做:

Put an index on the student count and simply do:

select t.*
from teachers t
order by student_count asc
limit 1;

我不是触发器的忠实拥护者，但是当它们有用时，它们确实是有用的.您遇到性能问题，并且需要沿层次结构聚合数据.您需要学生上insert，update和delete的触发器.

I am not a big fan of triggers, but when they are useful, they really are useful. You have a performance problem and a need to aggregate data along a hierarchy. You need triggers for insert, update, and delete on the students.

这篇关于MySQL-获取具有最低关联计数的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！