Export list of records that are there in table 1 and not there in table 2

[Jyothi]

Hi

 

Am a beginner in web development and trying to learn it in depth. Am doing some training assignments in MySql and stuck in it. I need serious help from professionals in web development.

 

My problem is :

I have 2 tables , which are almost identical and the number of records in the table is huge (more than 10 lakhs). Am working on writting a query that check records that are there in table 1 and not there in table 2 and export the list. But, the problem am facing here is , i want to write a query that is fast as there are more than 10 Lakhs records.

 

I have used the below piece of logic in mysql script

If ((|A| = |B|) && |A U B| = |A|)) ====>>> A = B

 

|A| = NUMBER of rows in A

|B| = NUMBER of rows in B

 

But this doesn't seem to be Fast and efficient one. Please help me to export a list of records that are not present in table2 "Fast".

 

waiting for help!!

 

Thanks in advance

Jyothi

[Barand]

If tables have "keycol" whose values match when records are present in both tables, then

 

SELECT a.* FROM table1 a

LEFT JOIN table2 b ON a.keycol = b.keycol

WHERE b.keycol IS NULL

[Jyothi]

Hi Barand

 

Thanks a ton for the reply. But, i dont have a "keycol" present in the table. Am sorry for not clearly putting out all the details regarding my table and problem. Here it is:

 

I have 2 tables : table1 and table2, both have the same structure , like :

 

Sl.No.  |  Book Title

 

Table1 has approximately 10 million records and table2 has approximately 7 million records. Am working on building a single query that check records that are there in table 1 but not there in table2 and export that list. The problem am facing here is the query has to be efficient and export the record list "fast" as there are a huge number of records.

 

So without the "keycol" entry in my table structure how can i write a efficient query that works fast?

 

Please please help me with this!  :'(

 

Appreciate your help. Thanks in advance.

 

Regards

Jyothi

[Jyothi]

Am not sure , but i guess writting a sub-query would help in this? Not sure how to go about.

 

Please help!

[Barand]

Looks like "SI No" might be a candidate for the "keycol"

[Jyothi]

Thanks once again. SlNo is the candidate to check the query. I tried running the query, but it seems like it's still not fast. Looks like it;s taking a huge time! The time taken is almost >30 mins and yet the query is still not completely executed! The number of records in my table1 is around 70,000 and so is in table2.

 

Please guide me to know whether the query can be run fast by any other method(if i understand right, may be by use of sub query or any thing else?) . Sorry if am wrong in understanding any of theconcepts. Please help me!

 

Appreciate you help once again. Thanks a ton.

[Barand]

Do you have the SINo set as the primary keys in the tables? If not a PK , is there an index on that column?

 

PS Subqueries are usually slower

[Jyothi]

Oh no. The SlNo is not set as PK! Do i have to set it as PK ? If so how?

[Barand]

to set an index on SINo.

 

CREATE INDEX idx_sino
       ON tbl_name (SINo)

 

 

(Set the index on both tables.)

[Jyothi]

After setting index on slno on both the tables, the below query would show Empty Set though there are some records in table1 that aren't present in table2!

 

SELECT * FROM table1 a

LEFT JOIN table2 b ON a.slno = b.slno

WHERE b.slno IS NULL;

 

Wondering what could be wrong! :'(

[Jyothi]

Am wondering what's wrong!

[fenway]

After setting index on slno on both the tables, the below query would show Empty Set though there are some records in table1 that aren't present in table2!

 

SELECT * FROM table1 a

LEFT JOIN table2 b ON a.slno = b.slno

WHERE b.slno IS NULL;

 

Wondering what could be wrong! :'(

 

That's hard to believe... you will get at least as many records as are in table1.