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. 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 (note: generating data might take a long time, ~10 minutes)
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 DEFINER=`root`@`localhost` PROCEDURE `GenerateFakeData`(NrOfRows BIGINT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= NrOfRows DO
INSERT INTO tbl_data1 (a,b,c) VALUES ((SELECT floor(rand() * 1000) AS randNum1), (SELECT floor(rand() * 1000) AS randNum2),(SELECT floor(rand() * 1000) AS randNum3));
SET i = i + 1;
END WHILE;
END
and call it:
Call GenerateFakeData(200000);
Note: remember to change delimiter (e.g. DELIMITER //)!
-
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 more than 100 times faster now?
(6. Check the explain plan: did the DBMS use the index?)