# How to find the least optimised database query?

A library uses a relational database to store data about library books, their members and lending (books, which are currently borrowed). The database relations of this system are given below. Lengths of attributes are given within brackets.

Book(bookno(13), title(20), author(30), edition(7))
Lending(memno(4), bookno(13), duedate(8))


The library database currently has 10,000 book tuples and 1,000 member tuples. Assume that 400 book borrowings are on record and 10 copies of the bookno "81-203-1257-0" is on lend.

The following SQL query has been written to retrieve borrower's name with due date given the book no "81-203-1257-0".

SELECT m.*, l.*
FROM Member m, Lending l
WHERE m.memno = l.memno and bookno = "81-203-1257-0";


The following are relational algebra expressions to retrieve the information for the above query.

1. JOIN(Member and Lending Over memno) where bookno="81-203-1257-0")

2. JOIN(Member and (RESTRICT Lending where bookno="81-203-1257-0") over memno

3. CARTESIAN(Member and Lending) where bookno="81-203-1257-0")

4. RESTRICT(CARTESIAN (Member and (RESTRICT Lending where bookno="81-203-1257-0")) where Member.memno=Lending.memno

5. PROJECT m.*, l.* (RESTRICT Member m and Lending l where m.memno = l.memno and bookno = "81-203-1257-0")

which one is the least optimised and how to compare with it others?

I am newly comer to db and query optimization setup , I hope some expert, explanation answer with query trees?

• You need to give attribution for this exercise problem. Who's the author? – Raphael Nov 13 '16 at 15:46
• We discourage posts that simply state a problem out of context, and expect the community to solve it. Assuming you tried to solve it yourself and got stuck, it may be helpful if you wrote your thoughts and what you could not figure out. It will definitely draw more answers to your post. Until then, the question will be voted to be closed / downvoted. You may also want to check out these hints, or use the search engine of this site to find similar questions that were already answered. – Raphael Nov 13 '16 at 15:46
• @Raphael Thank you sir,..! I understand all five are correct, but I didn't understand how to draw, query trees part 4,5 . I really stuck , so , how to find least optimized one ,using heuristic analysis with out 4,5 . I feel part 3 , is take lot of memory, but I couldn't confirm my answer , yet. – uma Nov 13 '16 at 16:23
• jcsites.juniata.edu/faculty/rhodes/dbms/queryopt.htm – uma Nov 15 '16 at 1:49