Exercise: Indexing and creating a procedure
(see IDPLabs.pdf, Part III-Index Design and PerformanceLabs, p. 23)
Main things to be done for testing the effect of creating an index
- create a database and tables that are used, e.g. http://www.dbtechnet.org/labs/idp_lab/IDPLab1-CreateDatabase.sql (p. 24)
- Scenarios to be tested: p. 25-33
- INDEX command: p. 34-38
- create a procedure => apply it for generating data (check the video)
- generate at least 100 000 rows in order to see the effect of creating an index
An example
- First create a table:
CREATE TABLE tbl_data1
(
a
int(10) DEFAULT NULL,
b
int(10) DEFAULT NULL,
c
int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
- Then create a procedure which generates data:
CREATE PROCEDURE GenerateFakeData()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 100000 DO
INSERT INTO tbl_data1 (a,b,c) VALUES ((SELECT floor(rand() * 1000) AS randNum), (SELECT floor(rand() * 1000) AS randNum),(SELECT floor(rand() * 1000) AS randNum));
SET i = i + 1;
END WHILE;
END
Note: remember to change delimeter!
-
Run a SELECT command, e.g. find all the a, b and c values in which b is between 100 and 120 sorting the data by using a (ascending) => make a notice how much time the query took
-
Create a three star index for the query
-
Make the SELECT command again (but change the values, e.g. b is between 200 and 220, so that the result is not coming from the buffer memory) => make a notice how much time the query took (was the query approximately 100 times faster now?)
-
Check the explain plan: did the DBMS use the index?