mysql - Performance improvement for SQL count query -

what type of sql query use turn following;

|  id  |  serial  |  lcn  | initlcn | |------|----------|-------|---------| |  1   |        |  a1   |         | |  2   |    b     |  a2   |         | |  3   |    c     |  a3   |   a1    | |  4   |    d     |  a4   |   a2    | |  5   |    e     |  a5   |   a1    | |------|----------|-------|---------| 

into result similar this;

|  id  |  count  | |------|---------| |  1   |    2    | |  2   |    1    | |------|---------| 

using low sql skills, have managed write below query extremely slow;

select   a.id,   count (b.id) parent assets  left join assets b   on (a.lcn = b.initlcn) group a.id order a.id; 

select t1.id, t1.lcn, count(*) table1 t1 inner join table1 t2 on t1.lcn = t2.initlcn group t1.lcn 

see working live in sqlfiddle.