MCS-274 Lab 5 (Spring 2005)

Objective

You will experiment with the impact of indexing on performance. You may do this lab in pairs, which will give you fewer other users to coordinate timing runs with, so as to avoid throwing each other's timing off too much through system loading effects.

Specific goals

1. Create a database to work in, and in it create a table :

   create table OPTED (
     word varchar(50) not null,
     part varchar(50) not null,
     definition text not null
     );
   

   This table will hold entries from OPTED, The Online Plain Text English Dictionary, a list of words, their grammatical parts of speech (such as noun), and their definitions, produced by Ralph S. Sutherland from the Project Gutenberg version of the 1913 edition of Webster's Unabridged Dictionary.

2. Perform each of the following tasks, and record not only the SQL code you used and the results you got, but also the time that MySQL reported the task took. Repeat the tasks, so that you can see how repeatable the results are. You may want to control system load by cooperating with the other students.

   1. Load in the data using the command

      load data local infile '/Net/solen/home/m/a/max/MCS-274/OPTED.data' into table OPTED;
      

   2. Find out the definitions of the word 'computer'.
   3. Find all the words that start with 'comput'.
   4. Find out how many entries are in this dictionary.
   5. Find out how many words are in this dictionary. (Some may have more than one entry. You should already have seen an example of that.)
   6. Find the 10 words that appear last in the dictionary. (Remember that this is different from the last 10 entries.)
   7. Delete all rows from the table.

3. To make some of these operations faster, you could add an index to the table. Do so, and repeat your timings. Again, make sure you see how repeatable the times are. What operations got faster? How much? Can you explain why? If not, you might find it useful to put the word explain before select.

4. Did any of the operations get slower? Which ones? How much? Can you explain why?

Lab report

Write a report that is concise and assumes an audience familiar with your assignment and the technology, but which addresses all the above topics in an organized fashion.