SELECT DISTINCT(ibf_posts.author_id), ibf_topics.* FROM ibf_topics LEFT JOIN ibf_posts ON (ibf_topics.tid=ibf_posts.topic_id AND ibf_posts.author_id=25869) WHERE ibf_topics.forum_id=4 and ibf_topics.approved=1 and (ibf_topics.pinned=1 or ibf_topics.last_post > 0) ORDER BY pinned DESC, last_post DESC LIMIT 0,30 PLease suggest me the best possible optimize sql. Thanks in advance for any help. Subikar
Well first question would be do you have indexes on the following columns ibf_posts.topic_id ibf_posts.author_id ibf_topics.forum_id These could be indexed as well depending on the situation ibf_topics.approved ibf_topics.pinned But considering this is IBF maybe it's something to talk to them about if you're seeing slow query performance with this board as your database gets very large?
Is indexing will help to make it faster. anything more I have to do in the table to change or any sql changes do I need ?? Thanks, Subikar
yes buddy indexing makes it very fast only thing is you should know what u r doing Read this http://www.databasejournal.com/features/mysql/article.php/1578331 Regards Alex
Really thank you. I am weak in sql trying to do it myself. So that I can learn. Thank you very much I am doing if need any help will ask you. And if it successful I will let you know. Any more suggestion pls help me. Thanks, Subikar
php and mysql is very good comination to start with and its not very tough. Donot think we know everything ,even i am still learning and its a continuous process. Google the info whatever you have ,try to read on forums,tutorial i know it will be difficult to start like this but this works! I wish you best of Luck and Happy New Year Regards Alex
i'm not really an expert on optimization but let me try SELECT DISTINCT(ibf_posts.author_id), ibf_topics.* FROM ibf_topics LEFT JOIN ibf_posts ON (ibf_topics.tid=ibf_posts.topic_id AND ibf_posts.author_id=25869) WHERE ibf_topics.forum_id=4 and ibf_topics.approved=1 and (ibf_topics.pinned=1 or ibf_topics.last_post > 0) ORDER BY pinned DESC, last_post DESC LIMIT 0,30 since the query already stated that you are looking for ibf_posts.author_id=25869 so instead of using SELECT DISTINCT(ibf_posts.author_id) why not using SELECT only and rearrange the WHERE clause to SELECT ibf_topics.* FROM ibf_topics LEFT JOIN ibf_posts ON (ibf_topics.tid=ibf_posts.topic_id) WHERE ibf_topics.forum_id=4 and ibf_topics.approved=1 and ibf_posts.author_id=25869 and (ibf_topics.pinned=1 or ibf_topics.last_post > 0) ORDER BY pinned DESC, last_post DESC LIMIT 0,30
SELECT ibf_topics.* FROM ibf_topics LEFT JOIN ibf_posts ON (ibf_topics.tid=ibf_posts.topic_id) WHERE ibf_topics.forum_id=4 and ibf_topics.approved=1 and ibf_posts.author_id=25869 and (ibf_topics.pinned=1 or ibf_topics.last_post > 0) ORDER BY pinned DESC, last_post DESC LIMIT 0,30 From this sql I am not getting the exact value what the previous sql I am getting. This your sql is returing the null value. I need more help from you. Thanks, Subikar
i think ibf_posts.author_id=25869 should not be set literally to ibf_posts.author_id=25869 since it should be dynamically stated by the script. could i see the real script portion? what type of value were you expecting?