0

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?

  • 1
    Are you missing a GROUP BY? – Vesa Karjalainen Oct 9 '19 at 10:18
  • 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. – Akina Oct 9 '19 at 10:21
  • @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? – D3 K Oct 10 '19 at 5:03
  • 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. – Rick James Oct 15 '19 at 19:03
  • @RickJames I have added the GROUP BY clause as it was causing some confusion. Any feedback on second part of the question? – D3 K Oct 16 '19 at 6:17
0

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.

  • I tried it but it makes the query too slow when there are millions of students. – D3 K Oct 16 '19 at 6:19
  • How many schools? – Rick James Oct 16 '19 at 21:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.