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

  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 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!

  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 approximately 100 times faster now?)

  4. Check the explain plan: did the DBMS use the index?