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