Re: Optimizing left outer join query

[Toa Sty]

That was a quick reply :)

To be honest Muhammad I'm not sure what's best now. To do your query for that vendor_id is always gonna take 400K index lookups, so your goals have to be to speed up the index lookups as much as possible.

Things to consider are:
* Use temp tables as their index lookups can be faster (as I did ealier)
* Do lookups on an integer columns rather than varchar(25). Integers are much smaller so you should see faster index lookups. (so maybe look at normalising so that you have a an integer id for each mfg_no that you have)
* Combine the two. (join on an integer using a temp table! (might not help any more - I dunno)
* If you dont want to do this, you might (possibly) see some benefit to changing your varchar(25) to char(25) to get fixed row widths, but given that your query is just looking at index files I'm not sure that this will help. In fact it might make it worse. You can always try it and see. (It will probably be a good idea to run optimize after the col type change)

There may well be some other stuff you can do too, but I don't think that query is ever going to be super fast. It just has too much work to do.

HTH,
Toasty

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



Edited 1 time(s). Last edit at 09/26/2006 04:41AM by Toa Sty.