SQL optimization: "multiple queries" vs. "big join"

[mihai11]

Originally Posted by Eikinskjaldi

I am assuming you have some kind of where clause to limit the join, so it is not an all - all query

I do have a "WHERE" clause, but that is not of much help. If there are no errors in the database then each story MUST have a corresponding thread. The only thing that will actually limit the number of returned rows is a "LIMIT clause".

Originally Posted by Eikinskjaldi

Assuming there is some kind of index field that relates stories to threads, then the number in each table is irrelevant, the index and join will ensure you only reference the needed rows.

I am using an index, but I do not agree with your conclusions. If I am using the "JOIN", then for 10.000 stories there will be 10.000 threads that will be selected. In the database there will be much more than 10.000 threads because a thread can exist without a story, but a story cannot exist without a thread. (in fact, a thread will hold comments for a given story).

I agree with you that the join will not join all the threads with all the stories, but the joined number of stories and threads is still very big. With my algorithm I will select 10 stories at a time (only 10 stories are displayed on a page) then I will select the required data for each story.

Originally Posted by Eikinskjaldi

As I said the first time around. Don't wave your hands and guess, use explain and find out the actual cost!

I am going to follow your advice on this.

Originally Posted by Eikinskjaldi

Without hotcopy you have to turn your server off to do a backup.

When I want to make a backup of my site I use this command:

mysqldump --opt --host=ABC-HOST -Q -uUSER -pPASSWORD db_name > db_file.sql

If I want to put the data back, I firstly stop the board (from admincp) then issue this command:

mysql -uUSER -pPASSWORD db_name < db_file.sql

I am never stopping the mysql server.