left join query very slow

[php_begins]

I am joining 2 tables post and thread. And I need to populate the values from the post table. I did the following left join, but it seems very inefficient and very slow. Is there an alternate way to make it more effiecient and fast?

 

$getposts=mysql_query("SELECT post.username,post.dateline,post.postid,post.threadid,thread.threadid,thread.forumid from post LEFT JOIN thread ON post.threadid=thread.threadid WHERE thread.forumid='$id'  ORDER by postid DESC LIMIT $default_limit");

 

 

[Psycho]

You would have to provide more details regarding your database structure. And, this post belongs in the MySQL forum [moving] - not the PHP forum. You can also do an "EXPLAIN" on your query in MySQL to see what it returns (google it).

 

EDIT:

 

I see nothing in that query on the face of it that should cause a problem. Are you running this query in a loop?

[php_begins]

The structure of post table is as follows:

Field Type                         Null Key Default Extra

postid int(10) unsigned NO PRI     auto_increment

threadid int(10) unsigned NO MUL 0

parentid int(10) unsigned NO 0

username varchar(100) NO

userid int(10) unsigned NO MUL 0

title varchar(250) NO MUL

dateline int(10) unsigned NO MUL 0

pagetext mediumtext YES

 

The structure of thread table is as follows:

Field  Type                Null Key Default Extra

threadid int(10) unsigned NO PRI     auto_increment

title varchar(250) NO MUL

firstpostid int(10) unsigned NO 0

lastpostid int(10) unsigned NO 0

lastpost int(10) unsigned NO MUL 0

forumid

 

I want to join the the 2 tables based on the forum id.

[fenway]

That's not EXPLAIN output.

[Psycho]

OK, after reviewing the query further, it doesn't make sense. Here is the query in a readable format:

 

SELECT post.username, post.dateline, post.postid, post.threadid,
       thread.threadid, thread.forumid

FROM post
LEFT JOIN thread
  ON post.threadid=thread.threadid

WHERE thread.forumid='$id'
ORDER by postid DESC LIMIT $default_limit

 

OK, the "LEFT JOIN" tells the query to include records from the LEFT table (i.e. posts) even if there are no corresponding records in the RIGHT table (i.e. thread). First off, it looks as if the post records are dependent on the thread (i.e. multiple posts can be associated with a thread). So, why are you using a LEFT JOIN? Do you really have posts that are not associated with a thread?

 

Further, you have a WHERE clause that is checking for threads that match a specific value. But, the JOIN is trying to include posts that are not associated with a thread - yet you put a specific condition on the records using a value in the thread. This is illogical. I don't see any reason to use a LEFT JOIN (if my understanding is correct). just use a normal JOIN.