73 ms to 5000 ms jump in query for no reason?

[phubaba]

I have a table that connects entries by 2 categories category_id (cid) and
associated_category_id (acid)

so the first entry is cid = 1 and acid=0 then the next connected to that is
cid=1 acid=2 and the next connected to that is cid=2 acid=3. So here is my
issue. when I am getting information from the second tier i'll do WHERE
tier2.category_id=1 and using left outer join, i can also get the infromation
in the tier1 category. This query takes about 78 ms:

(tier1=cat tier2=cat2 tier3=cat3)

SELECT
cat.category_id, cat.category_name, cat.sort_order as 'category_sort',
cat.mktg_browser_title as 'browser_title', cat2.mktg_browser_title as
'browser_title2',
cat2.category_id AS lev2_category_id, cat2.category_name AS
lev2_category_name, cat2.sort_order AS 'lev2_category_sort', cat2.description
AS 'lev2_description',
cat3.category_id AS lev3_category_id, cat3.category_name AS
lev3_category_name, cat3.sort_order AS 'lev3_category_sort', cat3.description
AS 'lev3_description'
FROM cm_categories cat
LEFT OUTER JOIN cm_categories cat2
ON cat2.associated_category_id = cat.category_id
LEFT OUTER JOIN cm_categories cat3
ON cat3.associated_category_id = cat2.category_id
WHERE cat2.category_id = #variables.category_id#

And, if i want to get something from cat3 i do practically the same WHERE
call because everything is connected using outer joins so with a small
motification the query now looks like this:

SELECT
cat.mktg_browser_title as 'browser_title', cat2.mktg_browser_title as
'browser_title2',
cat3.mktg_browser_title as 'browser_title3',
cat.category_id, cat.category_name, cat.sort_order as 'category_sort',
cat2.category_id AS lev2_category_id, cat2.category_name AS
lev2_category_name, cat2.sort_order AS 'lev2_category_sort', cat2.description
AS 'lev2_description',
cat3.category_id AS lev3_category_id, cat3.category_name AS
lev3_category_name, cat3.sort_order AS 'lev3_category_sort', cat3.description
AS 'lev3_description',
cat3.associated_category_id as lev3_associated_categegory_id
FROM cm_categories cat
LEFT OUTER JOIN cm_categories cat2
ON cat2.associated_category_id = cat.category_id
LEFT OUTER JOIN cm_categories cat3
ON cat3.associated_category_id = cat2.category_id
WHERE cat3.category_id = #variables.category_id#

except that this query takes 5000 ms . WHY? I have absolutely no idea. I have
done the query without the WHERE and I get about 1000 entries. When I scan the
list I can usually find the cat3.category_id in about 3-4 seconds, WHY does the
computer do it in 5? There really seems to be nothing different between the
WHERE cat3.category_id=variable and the cat2.category call. I am wondering if
anyone has some insight to this issue because it is driving me absolutely nuts.

[rmorgan]

At first glance, the first thing that comes to mind is, what is the ID fields
datatype? If it is not a number it can take longer. If it is something other
than an integer you can try indexing the field column. But, it usually does not
do much good indexing if the column is already a integer.

[phubaba]

Yes it is an int. Good idea though, I just wish there was some way I could see
what the stupid query was actually doing, so I could pinpoint where the heck it
was messing up. There seems to be <<<<zero>>>>> debugging in sql which is so
rotten

[sblue]

Have you tried running the query in Query Analyzer? You can go to the "Query"
menu and select "Show Execution Plan" and you will know how the query is being
executed and where the bottleneck is. A table scan is often times the culprit
in terms of execution cost.

To view the execution plan click on its tab at the bottom of the query results
pane.

[sblue]

Assuming you are using SQL Server of course... ;)

[phubaba]

how would you run one of those say using like sqlyog? or is there a query code
for it.

btw I figured out the problem, all i had to do was make the category an index.
but i would still like to know how to do an analyzer query. thanks