SQL: How to optimize that query?

[yoda]

Hi everyone,

for this one ready are you?

Here's my situation, we use coldfusion 5 with a 2000 access database (I know, I know, but it does the job, so far...). In that database we have the following tables (an example):

links (hold all internal or external links)
- id (PK)
- text
- url

page1 (hold all links id that appear on page1, in the actual content, excluding menus and headers)
- id (FK to links.id) ONE-ONE

menuLinks (hold all links id that appear in the menu)
- id (FK to links.id) ONE-ONE

headerLinks (hold all links id that appear in the header)
- id (FK to links.id) ONE-ONE

there's more tables but you get the idea.

So what we want to do is grab all the links for let's say page1 here in our example. So we need links that appear in table page1, menuLinks and headerLinks and so on. (Note that duplicates among page1 and other tables are possible)

The following query is what I have so far but it's way to slow:

SELECT
l.id,
l.text,
l.url
FROM
links AS l,
page1 AS p,
menuLinks AS ml,
headerLinks as hl
WHERE
(p.id = l.id) OR
(ml.id = l.id) OR
(hl.id = l.id)

I looked at the different kinds of JOIN, but whitout any success so far. Any ideas?

Thanks a lot.

Yoda

[hasbeen]

Maybe I'm completely destroying the concept of data modeling here, but why all the tables? Why not add 2 columns to your links table (say "position" and "page") and use 'head', 'menu', 'page' as your input for the "position" column? This would enable you to query one table but still get the info you need (i.e. SELECT * FROM Links WHERE position='mid' AND page='1')

Of course, this is all dependent on you not having an Access db that's a Gig or larger...

BTW Yoda, welcome to the forum!

[gethan]

Another option is to run three seperate queries:

SELECT l.id, l.text, l.url
FROM links AS l, page1 AS p,
WHERE p.id = l.id

Access is probably struggling with the join across three tables... (maybe a cartesian join behind the scenes).

Anyway I think that the three seperate queries execution time will total a lot less than the one bad one.

[bmcgee]

Don't you really want:
SELECT l.id, l.text, l.url
FROM links AS l, page1 AS p
WHERE p.id = l.id
UNION
SELECT l.id, l.text, l.url
FROM links AS l, menuLinks AS ml
WHERE ml.id = l.id
UNION
SELECT l.id, l.text, l.url
FROM links AS l, headerLinks as hl
WHERE hl.id = l.id

This makes more sense than the ORing that you are doing I think.

This returns duplicate rows if you have ID in multiple tables. I think there might be a way around this (UNION ALL?).

Or you could do:
SELECT l.id, l.text, l.url
FROM links AS l, page1 AS p
WHERE l.id in (SELECT DISTINCT id FROM page1)
OR l.id in (SELECT DISTINCT id FROM menuLinks)
OR l.id in (SELECT DISTINCT id FROM headerLinks)

This will return you only one instance from the links table, even if that ID is in multiple tables (if the UNION ALL doesn't work in the above query).

Also, sorry if this seems like a dumb question, but you do have indexes on the ID field of all four tables, right?

[yoda]

sorry about the late reply, it's been a crazy week!

gethan, I tried your solutions and it runs way faster, thanks a lot. Now I'll go back to my cave and try your solution bmcgee. I would really like to be able to run that as one query and yes I do have index for all the id fields.

I'll let you know wich one is the most efficient.

thanks again.

yoda