Using INNER JOIN makes it slow?

[smbrr]

Hello,

I have a very basic query.

Table VI has 500k records, primary key is VI.ID, and the field I'm filtering is VI.sku, which has an index.

If I do:

select count(vi.id), vi.sku
from vi
where vi.sku="ABCD"
group by vi.sku

The query runs in less that a second and all is well.

However, my application is more complex and I want to allow the user to have one or two sku's, or maybe three. Even a hundred if he feels like it.

So I created a SKU_AUTH table that's being fed lines from the form the user sees. Its only field and primary key is SKU_AUTH.sku

Same situation, the user just wants "ABCD" so SKU_AUTH has 1 line ("ABCD")

I run the query:

Select count(VI.ID), VI.sku
from VI inner join SKU_AUTH on VI.sku=SKU_AUTH.sku
group by VI.sku

That query takes a good five minutes to return the result.

I don't understand why. Are inner joins inherently terrible for this kind of work, should I work with an IN() parameter instead?

 

[Guus2005]

Assuming nothing else has changed, I don't understand either. It should work almost as fast as the former. All indexes are in the right place, that would be my first guess. But since they are there the only thing i can think up is use the WHERE IN clause, as you suggested yourself.

Have you already tried that?

Select count(a.ID), a.sku
from VI a 
where a.sku in (select b.sku from SKU_AUTH b)
group by a.sku

HTH

 

[Galaxiom]

Where are the tables located? Joins between tables on different systems can be very slow.

 

[smbrr]

I think it's because of local network issues. The queries I run are starting to take completely random amounts of time to run, from 1s to 10min, and we did have a network outage yesterday.

Sorry for that panic thread, I'll update if it turns out it was not.