Skip to content

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

  1. 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;
  1. 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 //)!

  1. 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.

  2. Create a three star index for the query.

  3. 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?)