The efficiency of a query command in Relational Algebra

Query Efficiency

Fact:

The disk accessing time can be several milli-seconds long (i.e., to read data on disk takes several milli-seconds)
The processing time of queries is mostly disk access times to read the information !!!

Query optimization:

Different relational algebra expressions can result in the same result set !!!
Query optimization means finding the most efficient relational algebra expression that produces the desired result set

Efficiency consideration of queries

Consider the following Relational Algebra expression:

σ _{(dno = dnumber and dname = 'Research')} ( employee × department )

The steps of execution of this relational algebra expression is: (using an example relation for Employee and Department)

Employee: +--------+---------+----------+-----+ | fname | lname | salary | dno | +--------+---------+----------+-----+ | John | Smith | 30000.00 | 5 | | Frankl | Wong | 40000.00 | 5 | | Alicia | Zelaya | 25000.00 | 4 | | Jennif | Wallace | 43000.00 | 4 | | Ramesh | Narayan | 38000.00 | 5 | | Joyce | English | 25000.00 | 5 | | Ahmad | Jabbar | 25000.00 | 4 | | James | Borg | 55000.00 | 1 | +--------+---------+----------+-----+
Department: +----------------+---------+ | dname | dnumber | +----------------+---------+ | Research | 5 | | Administration | 4 | | Headquarters | 1 | +----------------+---------+
Exmployee × Department: +--------+---------+----------+-----+----------------+---------+ | fname | lname | salary | dno | dname | dnumber | +--------+---------+----------+-----+----------------+---------+ | John | Smith | 30000.00 | 5 | Research | 5 | | John | Smith | 30000.00 | 5 | Administration | 4 | | John | Smith | 30000.00 | 5 | Headquarters | 1 | | Frankl | Wong | 40000.00 | 5 | Research | 5 | | Frankl | Wong | 40000.00 | 5 | Administration | 4 | | Frankl | Wong | 40000.00 | 5 | Headquarters | 1 | | Alicia | Zelaya | 25000.00 | 4 | Research | 5 | | Alicia | Zelaya | 25000.00 | 4 | Administration | 4 | | Alicia | Zelaya | 25000.00 | 4 | Headquarters | 1 | | Jennif | Wallace | 43000.00 | 4 | Research | 5 | | Jennif | Wallace | 43000.00 | 4 | Administration | 4 | | Jennif | Wallace | 43000.00 | 4 | Headquarters | 1 | | Ramesh | Narayan | 38000.00 | 5 | Research | 5 | | Ramesh | Narayan | 38000.00 | 5 | Administration | 4 | | Ramesh | Narayan | 38000.00 | 5 | Headquarters | 1 | | Joyce | English | 25000.00 | 5 | Research | 5 | | Joyce | English | 25000.00 | 5 | Administration | 4 | | Joyce | English | 25000.00 | 5 | Headquarters | 1 | | Ahmad | Jabbar | 25000.00 | 4 | Research | 5 | | Ahmad | Jabbar | 25000.00 | 4 | Administration | 4 | | Ahmad | Jabbar | 25000.00 | 4 | Headquarters | 1 | | James | Borg | 55000.00 | 1 | Research | 5 | | James | Borg | 55000.00 | 1 | Administration | 4 | | James | Borg | 55000.00 | 1 | Headquarters | 1 | +--------+---------+----------+-----+----------------+---------+
σ_{(dno = dnumber and dname = 'Research')} ( Exmployee × Department ): +--------+---------+----------+-----+----------+---------+ | fname | lname | salary | dno | dname | dnumber | +--------+---------+----------+-----+----------+---------+ | John | Smith | 30000.00 | 5 | Research | 5 | | Frankl | Wong | 40000.00 | 5 | Research | 5 | | Ramesh | Narayan | 38000.00 | 5 | Research | 5 | | Joyce | English | 25000.00 | 5 | Research | 5 | +--------+---------+----------+-----+----------+---------+

Note:

This Relational Algebra expression σ_{(dno = dnumber and dname = 'Research')} ( Exmployee × Department ) will find:
(We will study this later - right now, we discuss efficiency of a query)

Consider an alternative expression:

σ _{dno = dnumber} ( employee × σ _{dname = 'Research'} ( department ) )

The steps of execution of this relational algebra expression is: (using an example relation for Employee and Department)

Employee: +--------+---------+----------+-----+ | fname | lname | salary | dno | +--------+---------+----------+-----+ | John | Smith | 30000.00 | 5 | | Frankl | Wong | 40000.00 | 5 | | Alicia | Zelaya | 25000.00 | 4 | | Jennif | Wallace | 43000.00 | 4 | | Ramesh | Narayan | 38000.00 | 5 | | Joyce | English | 25000.00 | 5 | | Ahmad | Jabbar | 25000.00 | 4 | | James | Borg | 55000.00 | 1 | +--------+---------+----------+-----+
Department: +----------------+---------+ | dname | dnumber | +----------------+---------+ | Research | 5 | | Administration | 4 | | Headquarters | 1 | +----------------+---------+
σ_{dname = 'Research'}( department ) +----------------+---------+ | dname | dnumber | +----------------+---------+ | Research | 5 | <----- 1 tuple !!! +----------------+---------+
Employee × σ_{dname = 'Research'}( department ) +--------+---------+----------+-----+----------+---------+ | fname | lname | salary | dno | dname | dnumber | +--------+---------+----------+-----+----------+---------+ | John | Smith | 30000.00 | 5 | Research | 5 | | Frankl | Wong | 40000.00 | 5 | Research | 5 | | Alicia | Zelaya | 25000.00 | 4 | Research | 5 | | Jennif | Wallace | 43000.00 | 4 | Research | 5 | | Ramesh | Narayan | 38000.00 | 5 | Research | 5 | | Joyce | English | 25000.00 | 5 | Research | 5 | | Ahmad | Jabbar | 25000.00 | 4 | Research | 5 | | James | Borg | 55000.00 | 1 | Research | 5 | +--------+---------+----------+-----+----------+---------+
σ_{dno = dnumber}( Exmployee × σ_{dname = 'Research'}( department ) ): +--------+---------+----------+-----+----------+---------+ | fname | lname | salary | dno | dname | dnumber | +--------+---------+----------+-----+----------+---------+ | John | Smith | 30000.00 | 5 | Research | 5 | | Frankl | Wong | 40000.00 | 5 | Research | 5 | | Ramesh | Narayan | 38000.00 | 5 | Research | 5 | | Joyce | English | 25000.00 | 5 | Research | 5 | +--------+---------+----------+-----+----------+---------+

Observation:

We got the same result !!!
But:

Without going into the processing details, I'm just telling you that the query (relational algebra expression):

σ _{dno = dnumber} ( employee × σ _{dname = 'Research'} ( department ) )

is more efficient than:

σ _{(dno = dnumber and dname = 'Research')} ( employee × department )

Final notes
- Query optimization is an important topic of Database Systems
- It will be discussed in the graduate Database course (CS554): click here
- In CS377, we are only concerned about: