Question: I have a table with around 100 million rows consisting of three columns (all INT):

```
id | c_id | l_id

```

Even though I use indices even a basic 

```
select count(*), c_id 
from   table 
group by c_id;

```

takes 16 seconds (MYISAM) to 25 seconds (InnoDB) to complete. 

Is there any way to speed up this process without tracking the count in a seperate table (e.g. by using triggers)?

/edit: all colums have indices

Answer: 
See execution plan for possible ways to do the same queries [SqlFiddle](http://sqlfiddle.com/#!2/821fc/3),

`SELECT COUNT(id)` will be faster if `c_id` is not indexed on the test set i have provided.

otherwise you should use `COUNT(*)` since optimization of index may not be used in the query.

**It is also dependent of the number of rows in the DB and the ENGINE type** , since mysql will decide what is better based on this fact also.

**You should always see the execution plan of the query** before executing it by typing `EXPLAIN` before the select.

I have to say that in most cases on big datasets, `COUNT(*)` and `COUNT(id)` should result in the same execution plan.
It's not the `Count(*)` that gives the performance issue but grouping on 100 million rows.

You should add an index on the c\_id column