Subject | Re: question about optimizing group by |
---|---|
Author | woodsmailbox |
Post date | 2008-11-21T13:18:18Z |
Ok, sorry, I stripped down the query to get understood to the point
that it didn't make sense. Please let me try again.
Below are 4 equivalent selects. Question is, wouldn't the last query
perform faster than the others?
select
c.company_id,
c.company_name,
count(1) as employee_count
from
companies c
inner join company_employees ce on ce.company_id = c.company_id
group by
c.company_id,
c.company_name -- SUPERFLOUS INCLUSION OF company_name in SORT
TABLE or INDEX SCAN
ALTERNATIVE SYNTAX #1
select
c.company_id,
min(c.company_name), -- SUPERFLOUS INDEX SCAN TO COMPUTE min()
count(1) as employee_count
from
companies c
inner join company_employees ce on ce.company_id = c.company_id
group by
c.company_id
ALTERNATIVE SYNTAX #2
select
c.company_id,
c.company_name,
t.employee_count
from
companies c
inner join (select company_id, count(1) as employee_count from company_employees group by company_id)
group by
c.company_id
ALTERNATIVE SYNTAX #3 with a hypothetical first() aggregate function:
select
c.company_id,
first(c.company_name),
count(1) as employee_count
from
companies c
inner join company_employees ce on ce.company_id = c.company_id
group by
c.company_id
that it didn't make sense. Please let me try again.
Below are 4 equivalent selects. Question is, wouldn't the last query
perform faster than the others?
select
c.company_id,
c.company_name,
count(1) as employee_count
from
companies c
inner join company_employees ce on ce.company_id = c.company_id
group by
c.company_id,
c.company_name -- SUPERFLOUS INCLUSION OF company_name in SORT
TABLE or INDEX SCAN
ALTERNATIVE SYNTAX #1
select
c.company_id,
min(c.company_name), -- SUPERFLOUS INDEX SCAN TO COMPUTE min()
count(1) as employee_count
from
companies c
inner join company_employees ce on ce.company_id = c.company_id
group by
c.company_id
ALTERNATIVE SYNTAX #2
select
c.company_id,
c.company_name,
t.employee_count
from
companies c
inner join (select company_id, count(1) as employee_count from company_employees group by company_id)
group by
c.company_id
ALTERNATIVE SYNTAX #3 with a hypothetical first() aggregate function:
select
c.company_id,
first(c.company_name),
count(1) as employee_count
from
companies c
inner join company_employees ce on ce.company_id = c.company_id
group by
c.company_id