DBMS
Index and Query
Optimization
1
Index and Query
Optimization
Abel M. Ingaw
Ronald T. Bragas
Index: a data structure that improves the speed of
data retrieval operations
MySQL index: a separate file that is sorted, and
contains only the field/s a user is interested in
sorting on
Indexing: a data structure, which orders data of
one or more columns in database table
Definition of Terms
2
Index: a data structure that improves the speed of
data retrieval operations
MySQL index: a separate file that is sorted, and
contains only the field/s a user is interested in
sorting on
Indexing: a data structure, which orders data of
one or more columns in database table
defined based on its indexing attributes
can be one of the following types
Primary Index: If index is built on ordering
'key-field' of file
Secondary Index: If index is built on non-
ordering field of file
Clustering Index: If index is built on ordering
non-key field of file
Indexing
3
defined based on its indexing attributes
can be one of the following types
Primary Index: If index is built on ordering
'key-field' of file
Secondary Index: If index is built on non-
ordering field of file
Clustering Index: If index is built on ordering
non-key field of file
Dense Index: there is an index record for every
search key value in the database
Sparse Index: index records are not created
for every search key. An index record here
contains search key and actual pointer to the
data on the disk
Clustering Index: If index is built on ordering
non-key field of file
Ordered Indexing
4
Dense Index: there is an index record for every
search key value in the database
Sparse Index: index records are not created
for every search key. An index record here
contains search key and actual pointer to the
data on the disk
Clustering Index: If index is built on ordering
non-key field of file
Multilevel Index:
Index records are comprised of search-key
value and data pointers. This index itself is
stored on the disk along with the actual
database files.
Ordered Indexing
5
Multilevel Index:
Index records are comprised of search-key
value and data pointers. This index itself is
stored on the disk along with the actual
database files.
Query Optimization
Part 2
6
Query Optimization
Query:
is a request for information from a database
Query Optimization
7
Query:
is a request for information from a database
Activity of choosing an efficient
execution strategy for processing query
Query Optimization
8
Activity of choosing an efficient
execution strategy for processing query
Aims to choose a query which minimizes the usage
of resources
Reduces the total execution time of query
Helps to find the near optimum solution
Query Optimization
9
Aims to choose a query which minimizes the usage
of resources
Reduces the total execution time of query
Helps to find the near optimum solution
Scenario:
Table Name: employee
Columns:
employee_number, firstname, surname, address ,
tel_no varchar, salary,overtime_rate
Query Optimization
10
Scenario:
Table Name: employee
Columns:
employee_number, firstname, surname, address ,
tel_no varchar, salary,overtime_rate
Example 1:
Find employee Fred Jone's salary(employee number
101832)
SQL:
SELECT salary FROM employee WHERE
employee_number = '101832';
Query Optimization
11
Example 1:
Find employee Fred Jone's salary(employee number
101832)
SQL:
SELECT salary FROM employee WHERE
employee_number = '101832';
Problem:
No clue where to find this record
Solution:
SELECT employee_number, firstname, surname FROM
employee WHERE employee_number= '10875';
Query Optimization
12
Problem:
No clue where to find this record
Solution:
SELECT employee_number, firstname, surname FROM
employee WHERE employee_number= '10875';
Example 2:
What about if you want to select on more than one
criteria?
SQL 1:
SELECT firstname FROM employee
Query Optimization
13
Example 2:
What about if you want to select on more than one
criteria?
SQL 1:
SELECT firstname FROM employee
Problem:
makes no use of an index at all. An index on firstname
is useless
Solution:
SELECT firstname FROM employee WHERE
surname="Madida";
Query Optimization
14
Problem:
makes no use of an index at all. An index on firstname
is useless
Solution:
SELECT firstname FROM employee WHERE
surname="Madida";
Example 3:
find all the employees where half their overtime rate is
less than 20 pesos
SQL 1:
SELECT firstname FROM employee WHERE
overtime_rate<20*2;
Query Optimization
15
Example 3:
find all the employees where half their overtime rate is
less than 20 pesos
SQL 1:
SELECT firstname FROM employee WHERE
overtime_rate<20*2;
Problem:
Every single employee record is being read
School Algebra to the rescue:
x/2 = y' is the same as 'x = y*2
Query Optimization
16
Problem:
Every single employee record is being read
School Algebra to the rescue:
x/2 = y' is the same as 'x = y*2
Solution:
Add an index on overtime_rate
SELECT firstname FROM employee WHERE
overtime_rate/2<20;
Query Optimization
17
Solution:
Add an index on overtime_rate
SELECT firstname FROM employee WHERE
overtime_rate/2<20;
Example 4:
Having thousands of records, delete all records as fast
as possible.
SQL 1:
DELETE FROM employee
Query Optimization
18
Example 4:
Having thousands of records, delete all records as fast
as possible.
SQL 1:
DELETE FROM employee
Problem:
DELETE drops records one by one
Solution:
TRUNCATE TABLE employee
TRUNCATE employee
Query Optimization
19
Problem:
DELETE drops records one by one
Solution:
TRUNCATE TABLE employee
TRUNCATE employee
Conclusion
It's not only getting the data in that
needs to be quick - sometimes you
need to get it out quickly too
20
It's not only getting the data in that
needs to be quick - sometimes you
need to get it out quickly too
Any questions?
End of Topic
21
End of Topic
