-1

I have a 1.1 million row table (150MB) where each row has a range of columns with one a column having 15 possible states that I am grouping by.

Any select with count is sub 0.05 seconds, for example:

SELECT variant, count(*) 
FROM `log`

As soon as I GROUP BY I go to to 0.7 seconds, for example:

SELECT variant, count(*) 
FROM `log` 
GROUP BY variant

The variant column has an index. The unique id per row is the primary key.

Not sure if my query is bad or that my web host has poor performance.

PS, I now installed MySQL on a high spec machine and it took 0.55 to run the query. So it look like it's not that slow.

  • 3
    Please provide SHOW CREATE TABLE log; I want to see the Engine and datatypes. – Rick James Jan 3 '16 at 6:03
2
SELECT variant, count(*) FROM log GROUP BY variant

together with

INDEX(variant)

will do a full scan of the index, not the table. That's 1.1M index rows, probably much less than 150MB.

0.7s is reasonable for scanning that much stuff.

If variant is an ENUM, that will take only 1 byte, hence will make the index smaller than if you had, say, VARCHAR(55). (I can discuss more after seeing SHOW CREATE TABLE log;.)

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.