David Perron wrote:
I have a query similar to the one below. Im trying to force a STRAIGHT JOING to Table5 which has an index on Id & Date. For some reason, the optimizer isnt picking the INDEX to execute the query (and subsequently doing a full scan on a 250 million+ row table) so Im trying to force the STRAIGHT JOIN so the order is executed as I indicate.
Is that one multicolumn index on (id, date) or two indexes for Table5? Is the full table scan on Table5? What leads you to believe that order is the determining factor? Have you tried EXPLAIN on your query to see which indexes the optimizer considers? In general, you'll have better luck posting the results of running EXPLAIN on your query than experimenting and asking, without details, why your experiment didn't work. In other words, there may be a better solution, but we don't have the info to see it.
Im having a problem with the syntax below. Can someone shed some light on exactly how I would mix these join types for this query.
I'm still not sure that mixing JOIN types is the solution, but do you get an error, or unexpected results? It helps to tell us which. OK, I see a syntax error...
SELECT
....... FROM
Table1
INNER JOIN Table2 USING (Id)
INNER JOIN Table3 USING (Id)
INNER JOIN Table4 USING (Id)
STRAIGHT JOIN Table5 ON (Table4.Id = Table5.Id)
INNER JOIN Table6 ON (Id)
WHERE Table1.Type IN (......)
AND Table5.Date >= '2004-04-01';
STRAIGHT JOIN should be STRAIGHT_JOIN, and STRAIGHT_JOIN does not take a join_condition. You have to put restrictions on a STRAIGHT_JOIN in the WHERE clause. See <http://dev.mysql.com/doc/mysql/en/JOIN.html> for more. Also, the last join_condition needs to be either "USING (Id)" or "ON Table5.Id=Table6.Id". Finally, it's not an error, but INNER is redundant here, as INNER JOIN = JOIN. So, you'd change this to something like:
SELECT ....... FROM Table1 JOIN Table2 USING (Id) JOIN Table3 USING (Id) JOIN Table4 USING (Id) STRAIGHT_JOIN Table5 JOIN Table6 USING (Id) WHERE Table1.Type IN (......) AND Table4.Id = Table5.Id AND Table5.Date >= '2004-04-01';
If that doesn't help, post the EXPLAIN output of your original query.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
