Multiple vs Single COUNT on same col + Multiple vs Single col index

Question

I have two very simple questions

1. Multiple vs Single COUNT on same col

I need to perform CASE on an aggregate column like this:

SELECT tbl_students.school, COUNT(tbl_students.id) as students, (
 CASE
 WHEN students > 1000 THEN 'SMALL'
 WHEN students > 10000 THEN 'MEDIUM'
 WHEN students > 100000 THEN 'LARGE'
 END
) as size 
FROM tbl_students 
GROUP BY tbl_students.school

But MySQL does not allow using the column alias within the SELECT part. So instead of above, I'll have to write:

SELECT tbl_students.school, COUNT(tbl_students.id) as students, (
 CASE
 WHEN COUNT(tbl_students.id) > 1000 THEN 'SMALL'
 WHEN COUNT(tbl_students.id) > 10000 THEN 'MEDIUM'
 WHEN COUNT(tbl_students.id) > 100000 THEN 'LARGE'
 END
) as size 
FROM tbl_students
GROUP BY tbl_students.school

My question is "will using COUNT multiple times on the same column actually performs COUNT multiple times? If yes, will it also result in performance issues when there are millions of records?".

2. Multiple vs Single col index

If a table has following three columns:

tbl_students(id, username, school, firstname, lastname)

And has composite unique key for username and school. My question is, do I need to create a separate index for school column if I am searching only in school column?

• Vesa Karjalainen: Are you missing a GROUP BY?
• Akina: 1) No, COUNT() will be calculated once. If you're afraid nevertheless you may use intermediate UDV. 2) No, if the index is (school, username) and yes if in backward order.
• D3 K: @VesaKarjalainen you are right but my question was limited to SELECT part so it would be good to avoid any unnecessary details. @Akina do you have any reference to what you are saying and what is intermediate UDV?
• Rick James: Either get rid of the school column or add GROUP BY school. What you have does not make sense. After that, we can look deeper into your question.
• D3 K: @RickJames I have added the GROUP BY clause as it was causing some confusion. Any feedback on second part of the question?

Answer 1

This is straightforward enough and more efficient:

SELECT school, 
 CASE
 WHEN students > 1000 THEN 'SMALL'
 WHEN students > 10000 THEN 'MEDIUM'
 WHEN students > 100000 THEN 'LARGE'
 END as size 
 FROM ( SELECT school,
 COUNT(*) as students
 FROM tbl_students
 GROUP BY school
 ) AS cts

It would need INDEX(school) or any index starting with school.

• D3 K: I tried it but it makes the query too slow when there are millions of students.
• Rick James: How many schools?