LEFT JOIN problem

[!NoItAll]

Total SQL Newbie here!

I have two tables I need to join. There are 34-thousand entries in table 1, 5-thousand entries in table 2. So - there are only 5-thousand records that need to be joined from table 2 to table 1.
I am exporting the data.
I need to preserve all 34-thousand records in my SQL statement - those with entries in table 2 and those without.

LEFT JOIN seems right to preserve all of the records - but executes so slowly it's rediculous (it takes like an hour or more)! If I use INNER JOIN it executes in about 12 seconds - very acceptable - but only returns records with entries in BOTH tables (5000 of them only).

Here is the statement I am using:

SELECT 
  assignments.AssignID,  
  assignments.Assignment,  
  assignments.Reporter,  
  assignments.Notes, 
  assignments.AssignDatetime,  
  assigncontents.Content AS 'content' 
FROM assignments 
LEFT JOIN assigncontents  
  ON assignments.AssignID = assigncontents.AssignID;
 

I am using MySQL 5.0.

I have read that indexing will help - but I don't know how to do that.
Is that something I can simply turn on in the administrator application - or is there a better way to write the statement to execute faster?

[coolsti]

Firstly, to get any useful help from a forum like this, post the structure of your tables. You can post the results of a "SHOW CREATE TABLE xxx" statement where xxx is substituted by your table name.

To get an idea of what kind of work MySQL needs to perform in a query, use the EXPLAIN syntax. You just add the word "EXPLAIN" to the front of your query. MySQL will not perform the query, but will tell you what it thinks it needs to do. Interpreting the outcome of the EXPLAIN is something that takes some understanding, though. The EXPLAIN will show you, for example, which indexes are being used, and how many rows in each table need to be examined. You certainly do not want to have all rows in all tables examined. You can try to add an index, run the EXPLAIN, see the effect, then try a different index, etc. etc.

By the way, you say that your LEFT JOIN is performing in 12 seconds and that is acceptable? That seems quite slow to me, especially if you are only joining two tables and there are less than 50000 entries in each table :) You should be able to do your select in well under a second.

Indexing is what you seem to need. Try to play around with some indexes. You don't want to add more than you need, as the indexes create additional tables, and make update and insert queries a bit slower. But adding the right indexes will speed up things maybe by a factor of a 100. As a hint, indexes are very useful for attributes or combinations of attributes that appear in where clauses (or on clauses, etc.).

Try adding an index on assigncontents.AssignID and see what happens. I assume that assignments.AssignID is already an index (as primary key for the assignments table?). If not, it should maybe be.

[Atli]

Hi.

This seems very strange to me. A query like that with less than 50.000 records shouldn't take more than a fraction of a second on decent hardware.

You say INNER JOIN is faster than LEFT JOIN?
That also very odd. In my experience, LEFT JOIN is usually much faster.

To make any sense of this, we would need to see the structure of your tables, as well as the EXPLAIN output for the query.

[!NoItAll]

Thank you all! Indexing was the trick. Sadly I am so new I didn't know what that meant fully - but with your help I felt a little more confident to dig into the UI (I'm using the GUI tools for Windows - they are really pretty elegant).
I added an index to the assigncontents.AssignID column and VOILA!
Where it would have taken a day to complete the query it now takes 4.9 seconds.
Yes - that may be a little bit slow - but perfectly acceptible considering that I am just exporting existing data. (my machine is only a 512MB 1GHZ unit)
The funny thing is - these tables were MADE to be LEFT JOINED so the company that made the software should have picked up on this problem. Pulling out one record at a time and waiting 10 to 15 seconds is probably not a big concern for them.
Thanks guys/gals! YOU ROCK!

Des

P.S. Yes - I do feel the sting of irony with my username and avatar!

[r035198x]

...

Des

P.S. Yes - I do feel the sting of irony with my username and avatar!

Feel free to resurrect the old handles thread if you want.