Multiple vs Single COUNT on same col + Multiple vs Single col index 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 &gt; 1000 THEN 'SMALL'
     WHEN students &gt; 10000 THEN 'MEDIUM'
     WHEN students &gt; 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) &gt; 1000 THEN 'SMALL'
     WHEN COUNT(tbl_students.id) &gt; 10000 THEN 'MEDIUM'
     WHEN COUNT(tbl_students.id) &gt; 100000 THEN 'LARGE'
  END
) as size 
FROM tbl_students
GROUP BY tbl_students.school

My question is &quot;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?&quot;.
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?
This is straightforward enough and more efficient:

SELECT  school, 
        CASE
           WHEN students &gt; 1000 THEN 'SMALL'
           WHEN students &gt; 10000 THEN 'MEDIUM'
           WHEN students &gt; 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.