I was wondering if you could explain a little more what is the best choice for a view algorithm. Reading on Google and MySQL Dev site did not helped me to clear my concerns, so i was hoping one expert could enlighten me.
The scenario is simple:
I create a view that pulls data from 5 columns, some of them being JOINed from other tables:
SELECT thread.threadid, thread.threadtitle, post.postid, forum.forumtitle, user.username
LEFT JOIN post ON (thread.threadid = post.threadid)
LEFT JOIN user ON (thread.userid = user.userid)
LEFT JOIN forum ON (thread.threadid = forum.threadid)
Then I run a simple view:
SELECT * FROM view WHERE dateline < 84600
I have indexes on threadid, postid and forumid columns.
What algorithm you would use for the above case? UNDEFINED, MERGE or TEMPTABLE? Could you you please explain why also? I currently use TEMPTABLE, because the view is used into a cronjob that is performed every 10min. The idea is to release the table locks as soon as possible, so TEMPTABLE (from my understanding) will create a temporary table with the view data, then release then locks and push the SQL data. Do you think using TEMPTABLE could affect the server load? Is it better to simply use UNDEFINED?
Thanks for your explanations.