Re: SQL Newbie needs urgently help! [optimizing query]

[Toa Sty]

If I've read your posts correctly, your problem is that your query is having to look at too many rows in the category table, so I wonder if we can get it to use a better index on this table.

Problem is, you only have 25 different values for p_catalogversion so in an 80K row table it's not going to restrict things much. You could try a multi-col index on (p_catalogversion, TypePkString) or the other way round I suppose. I wouldn't expect it to help much though.

Ah - I notice that you said that if you removed the p_catalogversion condition the query is much faster - so what does the explain plan look like in that case?

Toasty

BTW, you seem to have a duplicate key:
Categories_codeIDX covers p_code, but the multi col index
Categories_codeVersionIDX also covers it, which makes Categories_codeIDX redundant.
This shouldn't cause a problem but might save you some space if you remove it :)

-----------------------------------------
email: 'toasty'*3 at gmail