Better, faster query possible?

[kamurj]

hello, is there a way i can reformat this query to make it faster?

SELECT topic_id, topic_title, topic_poster, topic_type, topic_status, username, topic_replies, topic_views, bb_topics.forum_id, forum_name, post_text FROM bb_topics, bb_users, bb_posts_text, bb_forums WHERE topic_status!='2' and topic_type!='2' and bb_topics.forum_id!='640' and bb_topics.topic_poster=bb_users.user_id and bb_topics.forum_id= bb_forums.forum_id and bb_posts_text.post_id=bb_topics.topic_first_post_id ORDER BY topic_type DESC, topic_id DESC LIMIT 20";

[codeman]

2 ways to speed it up.

one way is to index your database differently, index the fields that are used in where and order by... but be aware that too many indexes can slow down your inserts and updates, even though they will speed up your searches. Probably the best bet would be to index frequently used fields in where and order by statements.

second is to alter the php function you use to call the query. this wouldn't speed up the sql at all, but it may seem faster. Instead of mysql_query() use mysql_unbuffered_query() so that results are returned before the query finishes... which might not be of much use depending on how your are displaying your data.

Hope that helps...

[ultraslacker]

Use EXPLAIN with the query to see the details and to locate the bottlenecks.