MySQL Forums
Forum List  »  Optimizer & Parser

Re: Simple Query - but explain worries me
Posted by: Jake Day
Date: November 01, 2005 05:13AM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
3419
November 01, 2005 01:26AM
2256
November 01, 2005 02:48AM
Re: Simple Query - but explain worries me
2123
November 01, 2005 05:13AM
2168
November 01, 2005 07:37AM
2234
November 01, 2005 08:03AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.