I've written these two queries for same operation, on the basis of time complexity, i want to know which one is efficient.
select Fname, Lname, Address
from (select * from department d, employee e where d.Dnumber = e.Dno) as a
where a.Dname = 'Research';
Edit: on the basis of my first comment i assume that the query in from clause will work as temporary table/view which i name as "a", and contain all the columns of both the tables and i use it.(and i don't know it's a efficient way to do it.)
or
select Fname, Lname, Address from employee
where Dno = (select Dnumber from department where Dname = 'Research');
Or there could be more efficient way to do it. Thanks.
Edit2:
SELECT Pnumber, Dnum, Lname, Address, Bdate
FROM employee
JOIN department d ON d.Mgr_ssn = employee.Ssn
JOIN project p ON p.Dnum = d.Dnumber
WHERE p.Plocation = 'Stafford';
i've this argument, can you tell me what's the flaw in this.,
let's there be 1000 rows in each PROJECT, DEPARTMENT, EMPLOYEE table,
then in the above query, compiler will choose in what order the join has to be applied (ABC, ACB,BCA, BAC,...) choosing the efficient one. But to select the best of all for the first, then second,... it has to perform all and compare it with each other, plus after that the order of the tuples involved would be same(not sure, maybe if it goes for EMPLOYEE, DEPARTMENT first it only have 100-150 rows, that entirely depends upon tables...right???). I never worked on live project situation, maybe there are advantages of JOINS(which there are, that why it's in advance release) which i'm not able to see. But i'm not convinced.
then in the below query let 250 comes out to be success, which then CARTESIAN PRODUCT with DEPARTMENT table and produce 2,50,000 tuples from which let's say 5,000 be RESULT, which then again CARTESIAN PRODUCT with 1000 tuples to produce 500,000.
SELECT Pnumber, Dnum, Lname, Address, Bdate
FROM (SELECT Pnumber, Dnum, Mgr_ssn
FROM department d, (SELECT Pnumber, Dnum
FROM project where Plocation = 'Stafford') p
WHERE d.Dnumber = p.Dnum) q, employee e
WHERE q.Mgr_ssn = e.Ssn;
So, my question is JOIN is to write query simply, and let compiler decides what's the efficient order. Whereas in the below one we've done the almost work for compiler.
and, i've one more question, WHERE clause applies only on one relation?, or let me rephrase, first FROM(along with all the JOINS) clause execute then where clause execute coz in that case the query with JOINS will be very expensive.
Thanks.
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `department` ADD INDEX `department_idx_dnumber` (`Dnumber`);
ALTER TABLE `employee` ADD INDEX `employee_idx_dno` (`Dno`);
SELECT
a.Fname,
a.Lname,
a.Address
FROM
(SELECT
*
FROM
department d,
employee e
WHERE
(
d.Dnumber = e.Dno
)
AND (
Dname = 'Research'
)) AS a
WHERE
1 = 1