Need someone to test a function for me

  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8388
  • Loc: USA

Post 3+ Months Ago

I wrote a function to create in index on/in a table in MySQL. The reason for this post is that I've never worked with indexes and don't know if I did this correctly. And if I did, I would like someone to explain what indexes, what they do... that stuff.
Code: [ Select ]
/*
* function create_index( string|array $tbl_name [, string|array $idx_name [, boolean $silent]])
*   @string /or\ array $tbl_name - The table name(s) to create the index on
*   @string /or\ array $idx_name - The index name for the table names
*   @boolean $silent - If set to true, the errors would be silenced and would
*     be stored into the error variable.
*
* Creates an index for table name(s) put into the script with the index name
* either generated automatically (if none provided) or using the ones provided.
*/

function create_index($tbl_name, $idx_name = null, $silent = false)
{
  // Checking if we are allowed to create an index
  if($this->all_funcs['CREATE_INDEX'] != 1)
  {
    trigger_error("Current MySQL user could not create index on $tbl_name. ", E_USER_ERROR);
  }
  
  // Initiating the IDX array
  $idx = array();
  
  // Checking if submitted information are of correct datatype
  if(is_array($tbl_name) && is_null($idx_name) || !is_array($idx_name))
  {
    // Iterating through the $tbl_name array and adding the name to the array
    foreach($tbl_name as $value)
    {
      $idx[$value] = 'IDX_' . $value;
    }
  }
  
  // Checking if both variables are arrays
  if(is_array($idx_name) && is_array($tbl_name) && !is_null($idx_name))
  {
    // Counting number of values in $tbl_name and $idx_name
    $tbls = count($tbl_name);
    $idxs = count($idx_name);
    
    // Checking if $tbls equal $idxs
    if($tbls == $idxs)
    {
      // Creating the IDX array holding the information
      for($i = 0; $i<=$tbls; ++$i)
      {
        $idx[$tbl_name[$i]] = $idx_name[$i];
      }
    }
    else
    {
      return false;
    }
  }
  
  // Checking if it's only one table to create index on
  if(!is_array($tbl_name) && !is_array($idx_name))
  {
    // Creating the IDX array for it.
    $idx[$tbl_name] = $idx_name;
  }
  
  // Checking if $idx_name is of illegal type
  if(!is_array($idx_name) || !is_null($idx_name) && is_array($tbl_name))
  {
    // Checking if the errors were silenced
    if($silent)
    {
      $this->error = 'The variable <strong>$idx_name</strong> is of illegal datatype in <strong>' . __FILE__ . '</strong> on line <strong>' . __LINE__ . '</strong>.';
    }
    else
    {
      trigger_error('The variable <strong>$idx_name</strong> is of illegal datatype ', E_USER_ERROR);
    }
    return false;
  }
  
  // Initiating the SQL array
  $sql = array();
  
  // Filling the SQL array with SQL query data
  foreach($idx as $tbl => $idx)
  {
    $sql[] = "CREATE INDEX {$idx} ON {$tbl}";
  }
  
  // Carrying out the action
  foreach($sql as $query)
  {
    // Carrying out the SQL queries and making sure that they are not false
    if(!$this->set_result_resource($query, true))
    {
      $return = false;
      break;
    }
  }
  
  // Checking if there were any errors in the script
  if($return === false)
  {
    
    // Checking if the errors were silenced
    if($silent)
    {
      $this->error = mysql_error() . ' in <strong>' . __FILE__ . '</strong> on line <strong>' . __LINE__ . '</strong>.';
    }
    else
    {
      // Echoing out the MySQL Error
      trigger_error(mysql_error(), E_USER_ERROR);
    }
  }
  
  // Returning true if there were no errors
  if($return != false)
  {
    return true;
  }
}
  1. /*
  2. * function create_index( string|array $tbl_name [, string|array $idx_name [, boolean $silent]])
  3. *   @string /or\ array $tbl_name - The table name(s) to create the index on
  4. *   @string /or\ array $idx_name - The index name for the table names
  5. *   @boolean $silent - If set to true, the errors would be silenced and would
  6. *     be stored into the error variable.
  7. *
  8. * Creates an index for table name(s) put into the script with the index name
  9. * either generated automatically (if none provided) or using the ones provided.
  10. */
  11. function create_index($tbl_name, $idx_name = null, $silent = false)
  12. {
  13.   // Checking if we are allowed to create an index
  14.   if($this->all_funcs['CREATE_INDEX'] != 1)
  15.   {
  16.     trigger_error("Current MySQL user could not create index on $tbl_name. ", E_USER_ERROR);
  17.   }
  18.   
  19.   // Initiating the IDX array
  20.   $idx = array();
  21.   
  22.   // Checking if submitted information are of correct datatype
  23.   if(is_array($tbl_name) && is_null($idx_name) || !is_array($idx_name))
  24.   {
  25.     // Iterating through the $tbl_name array and adding the name to the array
  26.     foreach($tbl_name as $value)
  27.     {
  28.       $idx[$value] = 'IDX_' . $value;
  29.     }
  30.   }
  31.   
  32.   // Checking if both variables are arrays
  33.   if(is_array($idx_name) && is_array($tbl_name) && !is_null($idx_name))
  34.   {
  35.     // Counting number of values in $tbl_name and $idx_name
  36.     $tbls = count($tbl_name);
  37.     $idxs = count($idx_name);
  38.     
  39.     // Checking if $tbls equal $idxs
  40.     if($tbls == $idxs)
  41.     {
  42.       // Creating the IDX array holding the information
  43.       for($i = 0; $i<=$tbls; ++$i)
  44.       {
  45.         $idx[$tbl_name[$i]] = $idx_name[$i];
  46.       }
  47.     }
  48.     else
  49.     {
  50.       return false;
  51.     }
  52.   }
  53.   
  54.   // Checking if it's only one table to create index on
  55.   if(!is_array($tbl_name) && !is_array($idx_name))
  56.   {
  57.     // Creating the IDX array for it.
  58.     $idx[$tbl_name] = $idx_name;
  59.   }
  60.   
  61.   // Checking if $idx_name is of illegal type
  62.   if(!is_array($idx_name) || !is_null($idx_name) && is_array($tbl_name))
  63.   {
  64.     // Checking if the errors were silenced
  65.     if($silent)
  66.     {
  67.       $this->error = 'The variable <strong>$idx_name</strong> is of illegal datatype in <strong>' . __FILE__ . '</strong> on line <strong>' . __LINE__ . '</strong>.';
  68.     }
  69.     else
  70.     {
  71.       trigger_error('The variable <strong>$idx_name</strong> is of illegal datatype ', E_USER_ERROR);
  72.     }
  73.     return false;
  74.   }
  75.   
  76.   // Initiating the SQL array
  77.   $sql = array();
  78.   
  79.   // Filling the SQL array with SQL query data
  80.   foreach($idx as $tbl => $idx)
  81.   {
  82.     $sql[] = "CREATE INDEX {$idx} ON {$tbl}";
  83.   }
  84.   
  85.   // Carrying out the action
  86.   foreach($sql as $query)
  87.   {
  88.     // Carrying out the SQL queries and making sure that they are not false
  89.     if(!$this->set_result_resource($query, true))
  90.     {
  91.       $return = false;
  92.       break;
  93.     }
  94.   }
  95.   
  96.   // Checking if there were any errors in the script
  97.   if($return === false)
  98.   {
  99.     
  100.     // Checking if the errors were silenced
  101.     if($silent)
  102.     {
  103.       $this->error = mysql_error() . ' in <strong>' . __FILE__ . '</strong> on line <strong>' . __LINE__ . '</strong>.';
  104.     }
  105.     else
  106.     {
  107.       // Echoing out the MySQL Error
  108.       trigger_error(mysql_error(), E_USER_ERROR);
  109.     }
  110.   }
  111.   
  112.   // Returning true if there were no errors
  113.   if($return != false)
  114.   {
  115.     return true;
  116.   }
  117. }

I haven't tested that function yet because I don't even know what indexes are and what they do. I once created a class filled with functions that has to do with MySQL (Like a Database Access Wrapper), but I'm not sure if this functionality of it even works.

The link to the class is this link.

Thank you.
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Posts: 3422
  • Loc: Richland, WA

Post 3+ Months Ago

Indexes make querying tables faster to put it simply
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8388
  • Loc: USA

Post 3+ Months Ago

Alright, could you please check if that function works properly?
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13502
  • Loc: Florida

Post 3+ Months Ago

You can think of indexes like mini-tables inside your tables. These mini-tables store sub-sets of information from your table that let the engine look things up without needing to scan every row in the table.

For instance, assume you have the following table.

SQL Code: [ Select ]
CREATE TABLE IF NOT EXISTS `indexes` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `is_true` tinyint(1) NOT NULL,
  PRIMARY KEY  (`id`)
)
  1. CREATE TABLE IF NOT EXISTS `indexes` (
  2.   `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  3.   `is_true` tinyint(1) NOT NULL,
  4.   PRIMARY KEY  (`id`)
  5. )


As the table sits right now, if you have 100 rows in the table and you want to return all of the rows where the is_true column is actually true, the engine would have to scan all 100 rows looking for rows where that value is true even if there are only 50 rows that the value is true.

Now, if you apply an INDEX on the is_true column like so,

SQL Code: [ Select ]
ALTER TABLE `test`.`indexes` ADD INDEX ( `is_true` )


then one of these mini-tables is created that keeps track of which rows have which value for the is_true column.

If you explore phpMyAdmin you'll see something called "cardinality", which is an approximation of how many unique values there are for a certain index. In the case of this is_true column where it's a BOOLEAN with a total of 2 possible values, your cardinality should be 2 at the most.

With that index applied though, the engine will not have to scan all 100 rows in the table, it will be able to check the index and grab the 50 rows with a value of true right away because the index keeps track of where rows with each value in that column are.

--

I'm not sure of the search terms at the moment, but if you search for database/index related terms at Ozzu and limit the authors to bigwebmaster and camperjohn, you'll come across some interesting discussions about indexes. :)

--

As for your function, I'm not sure it will really accomplish anything. I don't see anywhere for column names to be specified.
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8388
  • Loc: USA

Post 3+ Months Ago

Awesome, thanks. That was the first explanation of indexes that I actually understood :lol:

Post Information

  • Total Posts in this topic: 5 posts
  • Users browsing this forum: No registered users and 60 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.