SQL I have 3 tables which are: Book( key , year, title) size of 1400000, A( key

Question

SQL

I have 3 tables which are: Book(key, year, title) size of 1400000, A( key, .... , ....) size of 560000 datas and B(key, .. , ..) size of 850000 datas.

A and B are both type of Book. I am trying to count the number of titles first appeared in A and later appeared in B ( means, A and B are the same book, but A was published earlier year than B).

But my query takes forever (about 80 sec)

Select count(*) From A a, B b, Book b1, Book b2

WHERE (    b1.key = a.key AND b2.key = b.key AND b1.title = b2.title AND b1.year < b2.year);

How can I improve my query to achive better excution time?

Explanation / Answer

First of all there are 3 tables (Book,A,B) as mentioned in specification but in query 4 tables are mentioned (A,B,Book1 and Book2). To improve further try below :

Select count(*) from Book B12 Join A a1 on a1.key = B12.key Join B b1 on b1.key=a1.key and a1.year<b1.year;

Hope it improves the execution time as I used JOINs

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.