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

aggregatealiasindexMySQL

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?

Best Answer

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.

Related Question