Why do I include some WHERE restrictions in my ON clauses and encourage
others to do it, too? Because I believe that the WHERE clause is applied
AFTER the temporary virtual table (the result of applying all of the JOIN
and ON clauses in the full statement) is complete. That is also why I am
Shawn,
I see what you are saying, but I don't believe it works that way. If that
were the case, putting the condition in the ON clause rather than the WHERE
clause would be at the top of the list of recommended optimizations in the
manual, but it's not. In fact, the manual recommends against
You are absolutely right, I was giving the developers too little credit. I
humbly apologize to all who contributed to the query optimizer. It was
never my intent to impugn the fine work that has gone into MySQL, my
optimization strategy is merely a reflection of my own pessimism. Please
PROTECTED]
To: Eamon Daly [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Thursday, July 29, 2004 10:54 AM
Subject: Re: JOIN/WHERE and index confusion
No. a.a_id is used in the join to b, not for selecting records in a.
Eamon Daly wrote:
I would assume it would use reporting_id_t, since the WHERE
: Thursday, July 29, 2004 10:54 AM
Subject: Re: JOIN/WHERE and index confusion
No. a.a_id is used in the join to b, not for selecting records in a.
Eamon Daly wrote:
I would assume it would use reporting_id_t, since the WHERE
clause has both a.a_id and a.timestamp
.
Eamon Daly
- Original Message -
From: gerald_clark [EMAIL PROTECTED]
To: Eamon Daly [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Thursday, July 29, 2004 10:54 AM
Subject: Re: JOIN/WHERE and index confusion
No. a.a_id is used
You've yet to show us the output of EXPLAIN. You've summarized, but you
haven't showed us. It's difficult to help without all the data. Perhaps we
should start with the EXPLAIN for your simpler query:
EXPLAIN SELECT * FROM a, b
WHERE a.a_id = b.a_id
AND a.timestamp BETWEEN
Why do you expect moving the a.timestamp restriction from the WHERE clause
to the JOIN will help?
Michael
[EMAIL PROTECTED] wrote:
Yes, I can think of two things you can try in order to speed up your
query. First - try the STRAIGHT JOIN clause with one small but critical
change to your
reporting_t! What the heck am I
missing?
Eamon Daly
- Original Message -
From: Eamon Daly [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, July 28, 2004 10:58 AM
Subject: JOIN/WHERE and index confusion
Hi, all. I think I'm
PROTECTED]
Sent: Thursday, July 29, 2004 8:04 AM
Subject: Re: JOIN/WHERE and index confusion
Why should it use any other?
Timestamp is the only field in the where clause, and you are selecting
all fields.
The second index would require more index reads, and the third can't be
used
to satisfy
-
From: gerald_clark [EMAIL PROTECTED]
To: Eamon Daly [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Thursday, July 29, 2004 8:04 AM
Subject: Re: JOIN/WHERE and index confusion
Why should it use any other?
Timestamp is the only field in the where clause, and you are selecting
all fields.
The second
Hi, all. I think I'm misunderstanding something basic about
indexes. I have a SELECT like so:
SELECT *
FROM a
LEFT JOIN b ON a.a_id = b.a_id
JOIN c ON a.c_id = c.c_id
JOIN d ON c.d_id = d.d_id
JOIN e ON c.e_id = e.e_id
WHERE a.timestamp BETWEEN 2004010100 AND 20040101235959
GROUP BY c.d_id,
Subject: JOIN/WHERE and index confusion
Hi, all. I think I'm misunderstanding something basic about
indexes. I have a SELECT like so:
SELECT *
FROM a
LEFT JOIN b ON a.a_id = b.a_id
JOIN c ON a.c_id = c.c_id
JOIN d ON c.d_id = d.d_id
JOIN e ON c.e_id = e.e_id
WHERE a.timestamp BETWEEN
13 matches
Mail list logo
