Re: FN-FORUM: Sql query help

date posted 14th January 2004 10:11

> Yes that does seem to work although execution time is back up to 0.24
> seconds, because of the join I assume.

It's not so much the JOIN that is the problem as AFAIK the execution plan
for

> > > SELECT
> > > SUM( IF(i.barcode IS NULL,1,0) ) AS count,
> > > s.*
> > > FROM
> > > item AS i,
> > > supplier AS s
> > > WHERE
> > > i.id_supplier = s.id_supplier
> > > GROUP BY
> > > s.id

and

select count(p.id), s.* from supplier s
inner join products p on (p.supplier_id=s.supplier_id and p.barcode is null)
group by s.supplier_id

should be identical as the first contains a JOIN but just doesn't use the
JOIN syntax (if you see what I mean)

It's the LEFT OUTER JOIN/LEFT JOIN that is the problem. I'd be tempted to
play manually with the query first to see if you can get the execution time
down (although indexing the barcode field is something I'd try).

If you use this query

select count(p.id), s.* from supplier s
left outer join products p on (p.supplier_id=s.supplier_id and p.barcode is
null)
group by s.supplier_id

what happens if you
a) just select a single field from suppliers rather than all (i know this
isn't ultimately what you want, but i've found queries drop from 30s to
under a second by not getting more back than I need)
b) is there another check you can use for 'p.barcode is null' ? My brain is
under the impression that IS NULL has a performance hit (although it might
be MSSQL i'm remembering this from)
c) have you added any other selection criteria to the query (i.e. have you
put a WHERE clause on the end)

Andy