CSC220 Lab 6 MySQL -- 2010

--D. Thiebaut 01:54, 20 October 2010 (UTC)

---

This lab deals with indexes, and how they affect operations on tables.

Create a New Table

• Use PhpMyAdmin on hadoop110 or hadoop101 to create a new table. Call it lab6
  • Create a integer field, auto increment, primary index, called Id
  • Create a varchar field, with 40 characters, utf8-general-ci for collation, called Name

• Create the following php program in your main directory (no need for it to be in your public_html directory)

<?php

// fillTable.php
// D. Thiebaut
// fills a table with $N random strings
// The table name is $tableName
//

//----------------------------------------------------------
// Globals.  Enter your user information here
//----------------------------------------------------------
$user        ="220a-xx";   // EDIT THIS LINE!
$passwd      = "xxxx";       // EDIT THIS LINE TOO!
$host        ="xgridmac.dyndns.org";
$database    = "220a-xx";   // EDIT THIS LINE AS WELL!

$N = 500000;
$tableName = "lab6";


//----------------------------------------------------------
// create a random word of 15 characters
//----------------------------------------------------------
function randomWord() {
  $validchars = "abcdfghjkmnpqrstvwxyz";
  $word  = "";
  for ( $i=0; $i<15; $i++ ) 
     $word .= substr( $validchars, rand(0, strlen($validchars)-1), 1 );
  return $word;
}

//----------------------------------------------------------
// Main Program
//----------------------------------------------------------
//--- connect to DB ---
mysql_connect( $host, $user, $passwd ) or die( "Unable to connect to db\n\n" );
print "==> Connected to database\n";

//--- select database ---
$result = mysql_select_db( $database );

//--- Insert  $N items in table---
$magic = rand( 100, $N/3 );
for ( $i=0; $i < $N; $i++ ) {
  $qty  = rand( ); // random number
  $name = randomWord(); //. strval( rand( ) );
  if ( $i==$magic ) $temp = $name;
  if ( $i==$magic+10 ) $name = $temp;
  $query = "INSERT INTO  `$tableName` (`Name`)  VALUES ( '$name' )";
  $result = mysql_query( $query );

  //--- give feedback to the user ---
  ( $i % 1000 != 0 )  or print $i . "\n";
}

//--- done.  close connection ---
mysql_close();

?>

• Edit the program to reflect your user name and password.
• Run the program:

  php fillTable.php

• verify that it outputs the number of items it is inserting in the table as it goes along. Depending on the time of day, and how many people are inserting in the database, this may take a minute or two.

Searching the Table

• For each question below, generate a query that outputs the answer, and record each query's execution time.

Question 1

What are the first ten names stored in the table, when ordered in ascending alphabetical order. How long does it take to get the answer?

Question 2

Display all the names that start with the letter b. Again, measure the execution time.

Question 3

How many different names start with the letter b

Question 4

Are all the names unique?

Building An Index

• An index is normally defined at the time a table is created. You can also create an index after the fact, as we'll do now. Use PhpMyAdmin, select the lab6 table, and display its structure.

• Locate the Create an Index box, and click Go

• Create an index named nameIndex, (not unique), on the field Name (no size specified, so that it uses the full size of Name).

• Check that the structure of the table now reflects the new index.

Faster Queries?

• Run the same queries as you entered in the first part, and measure their execution time. How much faster, on the average are your queries executing now?

Something New

• Try the following query on your table

select  *, left( `Name`, 1 ) as `first` from `lab6`;

What is the left function computing?

• Use this new knowledge to show how many different names start with 'a', with 'b', with 'c'... and with 'z'. In other words, generate the list of 26 records where one field is a letter of the alphabet, and the other the number of `Names` in `lab6` that start with that letter.