473,324 Members | 2,196 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

LEFT JOIN problem

!NoItAll
297 100+
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.   assignments.AssignID,  
  3.   assignments.Assignment,  
  4.   assignments.Reporter,  
  5.   assignments.Notes, 
  6.   assignments.AssignDatetime,  
  7.   assigncontents.Content AS 'content' 
  8. FROM assignments 
  9. LEFT JOIN assigncontents  
  10.   ON assignments.AssignID = assigncontents.AssignID;
  11.  
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?
Sep 29 '08 #1
4 1863
coolsti
310 100+
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.
Sep 29 '08 #2
Atli
5,058 Expert 4TB
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.
Sep 29 '08 #3
!NoItAll
297 100+
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!
Sep 30 '08 #4
r035198x
13,262 8TB
...

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.
Sep 30 '08 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

13
by: StealthBananaT | last post by:
My database has two tables - films has 10,000 records and reviews has 20,000 records. Whenever I try to list all the films and the count of its reviews, MySQL locks and I have to restart the...
0
by: Marek Lewczuk | last post by:
Hello, I have a strange problem, maybe some of you will be able to explain me something. I use LEFT JOIN as a substitute for subselects. It's true that many subselects can be rewriten using LEFT...
5
by: Marek Kotowski | last post by:
In MySQL online documentation there are some examples with multi-tables left joins. But all of them are like this (taken from the documentation): SELECT ... FROM table1 LEFT JOIN table2 on...
1
by: Eric | last post by:
Hi Folks, Lets assume I have three tables. Their layout is as follows. Please note that tblPeople does not have an entry for Denver (this is my problem) tblCity _________________ CityName ...
1
by: Quarco | last post by:
Hi, Suppose I have a query like: SELECT products.name AS product, SUM(IF(stock.invoice=0,1,0)) AS in_stock, SUM(IF(shopcart.status=1,1,0)) AS reserved FROM products LEFT JOIN stock ON...
3
by: Dam | last post by:
Using SqlServer : Query 1 : SELECT def.lID as IdDefinition, TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour FROM serveur.Data_tblDEFINITIONTABLEDECODES def,...
7
by: Steve | last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three "Left Outer Joins" in order to return every transaction for a specific set of criteria. Using three "Left Outer Joins"...
7
by: deko | last post by:
SELECT tblTxAcct.TxAcctName, tblTxType.TxTypeName, Nz(qryTxAcctListCt.TxCount, 0) AS TxCt FROM (tblTxAcct INNER JOIN tblTxType ON tblTxAcct.TxType_ID=tblTxType.TxType_ID) LEFT JOIN qryTxAcctListCt...
3
by: Doug | last post by:
Hi, I'm more familiar with MSSQL than Access syntax and have run into a problem with correctly putting ( )'s around the joins in a 3 table query. I want to INNER JOIN lenders and accounts and...
9
by: shapper | last post by:
Hello, I am used to SQL but I am starting to use LINQ. How can I create Left, Right and Inner joins in LINQ? How to distinguish the different joins? Here is a great SQL example:...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.