CSU 08106: Advanced Database
Technologies
Lecture 3: Query Optimization
Dr. Lupiana, D
FCIM, InsEtute of Finance Management
Semester 1
Agenda:
• EXPLAIN
• OpEmizaEon Techniques
EXPLAIN
• The EXPLAIN statement provides informaEon
about how MySQL executes statements
• When used, MySQL displays informaEon from
the opEmizer about the execuEon plan
– It explains how it would process the statement,
including informaEon about how tables are joined
and in which order
1
EXPLAIN
• EXPLAIN works with SELECT, DELETE, INSERT,
REPLACE, and UPDATE statements
• EXPLAIN returns a row of informaEon for each
table used in the SELECT statement
– Tables are listed in the output in the order they
were read while processing the statement
– This means that MySQL reads a row from the first
table, then finds a matching row in the second
table, and then in the third table, and so on
4
EXPLAIN
• When all tables are processed, MySQL outputs
the selected columns and backtracks through
the table list unEl a table is found for which
there are more matching rows
• . The next row is read from this table and the
process conEnues with the next table.
2
EXPLAIN
• MySQL Workbench has a Visual Explain
capability that provides a visual
representaEon of EXPLAIN output
• The next row is read from this table and the
process conEnues with the next table.
EXPLAIN
• With the help of EXPLAIN, you can see where
you should add indexes to tables so that the
statement executes faster by using indexes to
find rows
OpEmizaEon Techniques: Specific Columns
• It is very common and very convenient to use
‘*’ when retrieving data from a table
– ‘*’ means ALL i.e. retrieving all columns of a table
SELECT * FROM table_name
• Although this is a pracEce, it is very rare to use
every a`ribute retrieved from a table
3
OpEmizaEon Techniques: Specific Columns
• Database uses resources to retrieve data from
a table and therefore the more columns are
retrieved the more resources are used
• Therefore, specify only columns that you real
want to use its data
SELECT col1, col2 FROM table_name
10
OpEmizaEon Techniques: Using Index
• In a database an index is a data structure that
improves the speed of operaEons in a table
• PracEcally, indexes are also a type of table,
which keeps primary key or index field and a
pointer to each record into the actual table
11
OpEmizaEon Techniques: Using Index
• Thus, create an index for a column used in
WHERE, ORDER BY and GROUP By clauses
• Since an index is a data structure, it must be
created before it can be used
• Once created, an index is implicitly used
– No need to call/invoke it, it is automaEcally
called/invoked
12
4
… Using Index: Create Index
• An index can be created using the following
syntax;
CREATE INDEX index_name ON
table_name(column_name)
13
… Using Index: Display Created Indexes
• SHOW is used to display indexes created on a
table
• Indexes of a table can be displayed by using
the following syntax;
SHOW INDEXES FROM table_name
14
… Using Index: Remove an Index
• DROP is used to remove an index from a table
• To drop an index the following syntax can be
used;
DROP INDEX index_name ON table_name
15
5
OpEmizaEon Techniques: Leading Wildcards
• LIKE clause in SQL is used to provide more
flexibility when your condiEon is searching for
a keyword or sequence of characters
• LIKE clause uses wildcards (i.e. ‘%’ symbol) to
imply ‘any character’
– For example names LIKE ‘%;n’ means searching for
names that starts with combinaEon of any le`ers
but ends with ‘En’
16
OpEmizaEon Techniques: Leading Wildcards
• Leading wildcard means a wildcard that is
presented before a searched sequence of
characters
– The wildcard used in this names LIKE ‘%;n’
example is referred to as a leading wildcard
• With LIKE clauses, a database engine performs
two checks i.e. begin and end of a text
17
OpEmizaEon Techniques: Leading Wildcards
• With leading wildcard, database engine
performs a full table scan (start to finish)
– With a leading wildcard, the engine first checks for
ANY character, which is always true
– Thus the engine will check all records to find the
specified sequence of characters
18
6
OpEmizaEon Techniques: Leading Wildcards
• Thus to opEmize a query avoid using LIKE
clause and if it is necessary DO NOT use
leading wildcards
SELECT col1, col2 FROM table_name WHERE
names LIKE ‘Tim%’
19
OpEmizaEon Techniques: Leading Wildcards
• With this example, a database engine checks a
sequence of characters first
– Not all records meet the first condiEon check
– Thus unlike the previous example, in this example
the engine will not perform full table scan
20
OpEmizaEon Techniques: ‘UNION’ Over ‘OR’
• As menEoned in the previous slides, MySQL
uses only one index per table in a given query
• Therefore using ‘OR’ in a condiEon that
involves two different columns, even if both
columns have indexes, MySQL will only use
index of the first column
– Thus MySQL will sEll have to do a full table scan
(start to finish) to test the second condiEon
21
7
OpEmizaEon Techniques: ‘UNION’ Over ‘OR’
• With UNION, a query is separated to two or
more queries depending on condiEons
– Thus indexes of columns used in the condiEons
will all be used thus increasing performance
• Although not in all cases, UNION ALL is
preferable
– UNION introduces more computaEons since it
first sorts records and then eliminates duplicates
22
OpEmizaEon Techniques: ‘UNION’ Over ‘OR’
• UnopEmized SQL statement
SELECT col1, col2 FROM table_name WHERE condiEon1 OR
condiEon2
• OpEmized SQL statement
SELECT col1, col2 FROM table_name WHERE condiEon1
UNION ALL SELECT col1, col2 FROM table_name WHERE
condiEon2
23
