Re: Simple Query - but explain worries me

[Jake Day]

Vangelis,

Thanks for the reply, but i don't see how this would work.

I've got around 130,000 header rows and 1.4mn detail rows, in an average 1-11 relationship(1 header to 11 detail rows)

All headers have a distinct race number, and there are no children without parents.

So whatever the numbers you put into the equations it is impossible( i think) to find a query that will return less rows from the larger table than from the smaller table.

ie > 300,000
small table selects around 40,000 rows, and uses these 40,000 to index-search the larger table to return 400,000 rows
< 400,000
small table selects around 130,000 rows and uses these 130,000 to index-search the larger table to return 1.3mn rows.

you can put in any values for the > and it still goes to the larger query first, which simply doesn't make sense??

in the > 300,000 example, the query is currently going to the larger database first, to extract the 400,000 rows, to then index-search the smaller table to extract the matching 40,000 rows....this surely can't be efficient?