MySQL Forums
Forum List  »  Optimizer & Parser

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

Hi, I'm really hoping that someone can help to explain this too me, as its got me a bit concerned.

I've got two servers set up, one running 5.0 and one running 4.1

I'm still very new to MySQL, but have played with DB2 for a few years

I've got a fairly simple database structure at the mo. Two tables, a "header" table, and a "detail" table, with a one-many relationship.

Primary key on header and detail is race_id, foreign key on detail is race_id

If i write a simple query like

select * from header a, detail b
where a.race_id = b.race_id

then the optimizer goes to the header(smaller) table first, but my biggest concern is that if i add in a predicate to the statement, like

select * from header a, detail b
where a.race_id = b.race_id
and a.race_id > 300000

the "new" 5.0 version goes to the biggest table first????? - which scares the hell out of me.
The explain shows that the query path goes to the BIGGEST table first, (table b) using the range.

On the old version 4.1 the explain shows that the path is through the smallest (correct) raceheader first, as a range.

I need to have 5.0 to take advantage of "stored procedures". But the detail table has 1.3 mn rows on it, so if the optimizer is going to send all queries there first, then i've got a serious problem?

I've optimized both tables, so it shouldn't be a "stats" problem.

Please can anyone advise whether i should be worried??

thanks in advance

jake day

Options: ReplyQuote

Written By
Simple Query - but explain worries me
November 01, 2005 01:26AM
November 01, 2005 02:48AM
November 01, 2005 05:13AM
November 01, 2005 07:37AM
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.