MYSQL : Nested INNER JOIN query

[ajp18]

Hi,

I get an error when I try to execute the following query:

SELECT DISTINCT Images.Path, MasterKey.MasterKey
FROM (MasterKey INNER JOIN Data ON MasterKey.MasterKey=Data.MasterKey) INNER JOIN Images ON MasterKey.MasterKey=Images.MasterKey
WHERE Data.SystemCode='AP2' AND MasterKey.FolderID=7093;

Any suggestions!!!

I have spend lot of time optimizing this query but I have not seen any success.

Thanks for the consideration

 

[sleipnir214]

One thing to try is to drop the parentheses.

Where does it say your SQL error appears?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[ajp18]

I tried dropping prantheses but still it did not get rid of the following error:


ERROR 1104: The SELECT would examine more rows than MAX_JOIN_SIZE. Check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is ok



Thanks for your reply

 

[sleipnir214]

That error is stating that the query is syntactically correct, but that the query is going to hit your server very hard. How many records are in the three tables?

If you want it to run the query, take the advice offered in the error message.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[ajp18]

Thanks for your reply.

Table MasterKey contains 8812 records
Table Data contains 18259 records
Table Tmage contains 17348 records

Total # of recordset in 3 Tables are: 44419

I tried executing both of the following queries:

SET SQL_BIG_SELECTS=1;

SELECT Images.Path, MasterKey.MasterKey
FROM MasterKey INNER JOIN Data ON MasterKey.MasterKey=Data.MasterKey INNER JOIN Images ON MasterKey.MasterKey=Images.MasterKey
WHERE Data.SystemCode='AP2' AND MasterKey.FolderID=7093;

but it takes 65 seconds.


Thanks

 

[sleipnir214]

What MySQL is probably complaining about is the fact that when you multiply the three sets of rows together you get 2791263847184.

If the query is slow, examine your indeces. Make sure all the columns in your "ON" clauses and "WHERE" clauses are indexed.

Your indeces may be what MySQL was complaining about in the original error.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[ajp18]

Thank you very mcuh