Performance improvement for SQL count query

Question

What type of sql query would I use to turn the following;

|  ID  |  SERIAL  |  LCN  | INITLCN |
|------|----------|-------|---------|
|  1   |    A     |  A1   |         |
|  2   |    B     |  A2   |         |
|  3   |    C     |  A3   |   A1    |
|  4   |    D     |  A4   |   A2    |
|  5   |    E     |  A5   |   A1    |
|------|----------|-------|---------|

into a result similar to this;

|  ID  |  COUNT  |
|------|---------|
|  1   |    2    |
|  2   |    1    |
|------|---------|

Using my low SQL skills, I have managed to write the below query however it is extremely slow;

select
  a.id,
  count (b.id) as parent
from assets a 
left join assets b
  ON (a.lcn = b.initlcn)
group by a.id
order by a.id;

Firstly use an INNER JOIN, it will only return results that match, also index the join columns and lastly make sure they are TEXT or big VARCHARS, trying using ids — We0, May 21 '13 at 09:34
@We0 - `INNER JOIN` reduced execution time from ~28s to ~1s. Thanks! — Dan, May 21 '13 at 09:39
You can also take a look here: http://stackoverflow.com/a/457274/2319383 — Wiktor, May 21 '13 at 09:41
@We0 I do not seem to have the ability to up vote comments yet. I think it requires more Rep. — Dan, May 22 '13 at 23:45

score 0 · Answer 1 · answered May 21 '13 at 09:38

0

select
t1.ID,
t1.LCN,
COUNT(*)
from
Table1 t1
INNER JOIN Table1 t2 ON t1.LCN = t2.INITLCN
GROUP BY t1.LCN

See it working live in an sqlfiddle.

answered May 21 '13 at 09:38

fancyPants

50,732
33
89
96

score 0 · Answer 2 · edited May 21 '13 at 09:48

0

maybe two table join is not nesecerry . try this

select 
a.id
,b.cnt
from assets a
join (
 select 
 initlcn
 count(1) cnt
 from assets
 group by initlcn
)
b on (a.lcn=b.initlcn)

edited May 21 '13 at 09:48

Wasif

522
2
6

answered May 21 '13 at 09:41

gufeng

26
2

1

And you joined thin air or what? :) – N.B. May 21 '13 at 09:56

score 0 · Answer 3 · answered May 21 '13 at 10:04

you can test following query also -

I have checked the execution plan for your posted query and the following query and I am getting good difference between both -

SELECT t_1.Id, t_2.Cnt
  FROM Assets t_1,
       (SELECT Initlcn, COUNT(*) Cnt FROM Assets GROUP BY Initlcn) t_2
 WHERE t_1.Lcn = t_2.Initlcn

Performance improvement for SQL count query

3 Answers3