SQL Experts?

[PetrolTed]

Any SQL experts out there that could look at a query for me?

It's a (what should be simple) 3 table join that results in a table scan and I can't work out a way around it.

[pdV6]

I don't use MySQL, but I'd be happy to take a look.

My money's on not enough or incorrect indexes, though.

[PetrolTed]

Ok, names changed to protect in the innocent:

Table 1: news (id, headline etc)
Table 2: relationship (newsId,catId) allows each story to appear in multiple categories)
Table 3: category (catId,parentCatId,description etc.) the different sections of PH

A query to pull the stories out for a single category is very efficient (all correctly indexed etc).

However, I want to pull out stories that have a category of 'Road Test' and a parent Category of 'Road Test' (i.e. bla bla ... WHERE catId=1234 and parentCatId=1234)

Can't seem to get my head around why it ends up in a table scan of the largest table (the news table).

[3200ac]

does your environment have any tool that shows the access paths used by the query? The Oracle equivalent is Explain Plan, which steps through the parts of your query to show you where/when it is/isn't using indexes, full table scans, and acts as a preview of your query as you test changes... helpful to paste the full query?
ta,
m

[FunkyGibbon]

what is the difference between catid and parentcatid? and do you have a separate table for the parentcatids?

[PetrolTed]

Yes, it has an 'explain' function which shows which keys are being used. I'm fully conversant with that.

What I can't get my head around is why introduction of the 'parentId' in the WHERE clause results in no index being used.

[ThatPhilBrettGuy]

Optimisers eh? I'm sure you know as a work-around you could produce a temp table and join news on that to get around it.

Not a big MySQL person, but with most DB's it's surprisingly easy to get the optimiser in a twist. Sometimes if it thinks it's going to look at only 20% of the rows in a table, it'll scan.

[FunkyGibbon]

PetrolTed said:

What I can't get my head around is why introduction of the 'parentId' in the WHERE clause results in no index being used.

Is parentid indexed in the table?

[pdV6]

PetrolTed said:

Yes, it has an 'explain' function which shows which keys are being used. I'm fully conversant with that. What I can't get my head around is why introduction of the 'parentId' in the WHERE clause results in no index being used.

Can't get the same effect using MSSQL, but then the CatId and ParentCatId are both implicitly indexed due to setting up a foreign key relationship between them.

Saying that, dropping the FK constraints still doesn't introduce a table scan...

[PetrolTed]

Tried it with and without.

[Plotloss]

So relationship has primary superordinate keys of news and category?

[pdV6]

PetrolTed said:

Table 3: category (catId,parentCatId,description etc.) the different sections of PH ... However, I want to pull out stories that have a category of 'Road Test' and a parent Category of 'Road Test' (i.e. bla bla ... WHERE catId=1234 and parentCatId=1234)

Actually, completely aside from your immeduiate problem, are you doing this right in the first place?

The pseudo-sql you've posted implies that you are looking for records where the news is of a category that has a parent category of itself. Surely there won't be any of these?

Aren't you after news records where the category is X OR its category's parent category is X?

[PetrolTed]

Ooops... sorry, that should read "OR".

[pdV6]

OK - I get a table scan by dropping the primary key constraint (CatID) on the Categories table (i.e. the "free" index that comes with the PK is now missing)

Are all your PK's in place and as you expect?

[ATG]

Am I getting this right? You're putting extra constraints on the contents of Table 3 and that is causing a full scan of Table 1 even though you're not joing those tables directly. Table 2 is sat in the middle. If that is the case, then the bottom line is that the query optimizer is making a crap choice of tackling the query. Never used MySQL, but I'd (a) see if the table statistics are up to date (b) see if your dialect of SQL supports hints to the optimizer (c) horrific, rephrase the SQL with explicit sub queries to try to get the join btwn table 2 and 3 complete before it looks at the join btwn the result and Table 1.

[PetrolTed]

ATG said:

Am I getting this right? You're putting extra constraints on the contents of Table 3 and that is causing a full scan of Table 1 even though you're not joing those tables directly. Table 2 is sat in the middle. If that is the case, then the bottom line is that the query optimizer is making a crap choice of tackling the query. Never used MySQL, but I'd (a) see if the table statistics are up to date (b) see if your dialect of SQL supports hints to the optimizer (c) horrific, rephrase the SQL with explicit sub queries to try to get the join btwn table 2 and 3 complete before it looks at the join btwn the result and Table 1.

That appears to be the case, yes. I've tried forcing indexes to no avail. I'll see if I can bodge it with a subquery.

[ATG]

Yeuch. Good luck. Had a similar problem with SQL Server a while ago. Reordering the joins in the from clause "cured" it which is nuts given you'd hope the optimizer might recognise that A & B & C is the same as B & A & C. The query was in a stored proc so at least the execution plan is fixed after it is first calculated, and it seems to have been stable ever since.

[RichardD]

PetrolTed said:

Ok, names changed to protect in the innocent: Table 1: news (id, headline etc) Table 2: relationship (newsId,catId) allows each story to appear in multiple categories) Table 3: category (catId,parentCatId,description etc.) the different sections of PH A query to pull the stories out for a single category is very efficient (all correctly indexed etc). However, I want to pull out stories that have a category of 'Road Test' and a parent Category of 'Road Test' (i.e. bla bla ... WHERE catId=1234 and parentCatId=1234) Can't seem to get my head around why it ends up in a table scan of the largest table (the news table).

I may have wrong end of stick here, but what I'd do with this sort of problem is.

query 1 = grab records where there is a direct link (CatID)
query 2 = grab records where there is indirect link (ParentCatID)
query 3 = union query to join queries 1+2 together.

If there is more than one level of parent (grandparent) then this won't work unless more queries are added for each level of parenting...

[PetrolTed]

Yep, tried the union route but the parentCatId select is the one that proves innefficient.

Anyway, am experimenting with the ideas presented.

Thanks for your help.

[sybaseian]

PetrolTed said:

Yep, tried the union route but the parentCatId select is the one that proves innefficient. Anyway, am experimenting with the ideas presented. Thanks for your help.

Ted,

what indexes do you have on the tables? It could be something as simple as the where clauses don't match an existing index which then causes a table scan.

Query tuning is the bain of my life......