Loud Baking

While I was optimizing the database for the application I am currently developing, I thoroughly check the differences between these two types, VAR and VARCHAR, so might as well share my notes here. Keep in mind that these are for MySQL 5.0.3 and later, things were different in certain cases before.

CHAR

• string, length from 0 to 255
• required storage depends on the set length
• values are right-padded with spaces to the specified length when stored
• trailing spaces are removed when values are retrieved
• values that exceeds the specified length are truncated, warning is generated (strict SQL mode disabled, otherwise error and not stored)

VARCHAR

• string, length from 0 to 65,535 (but subject to maximum row size which is shared among all columns)
• required storage depends on the stored value
• values are not padded when stored
• trailing spaces are retained when values are stored or retrieved
• values that exceeds the specified length are truncated, warning is generated (strict SQL mode disabled, otherwise error and not stored)

Finally, a couple examples to illustrate it all:

mysql > CREATE DATABASE sandbox;
mysql > USE sandbox;
mysql > CREATE TABLE vc (`c` char(3), `v` char(3));
mysql > INSERT INTO vc VALUES ('', ''), ('  ', '  '), ('a', 'a'), ('a ', 'a '), ('abc', 'abc');
mysql > SELECT CONCAT('(', c, ')'), CONCAT('(', v, ')');

That last query will return:

+---------------------+---------------------+
| CONCAT('(', c, ')') | CONCAT('(', v, ')') |
+---------------------+---------------------+
| ()                  | ()                  |
| ()                  | (  )                |
| (a)                 | (a)                 |
| (a)                 | (a )                |
| (abc)               | (abc)               |   
+---------------------+---------------------+

Notice how for CHAR the trailing space is removed. Another interesting thing to note, sorting and comparing CHAR and VARCHAR columns:

mysql > SELECT c = 'a ', v = 'a ' FROM vc;
+---------------------+
| c = 'a ' | v = 'a ' |
+---------------------+
|        0 |        0 |
|        0 |        0 |
|        1 |        1 |
|        1 |        1 |
|        0 |        0 |
+---------------------+

As you can see, it compares values without regard to any trailing spaces.

There, that should resume it all and hopefully help next time there is a field type to set. Did I forget to mention anything? Let me know in the comments.

When I was coding some shells today, and after getting used to the invaluable query logs when working with controllers/views, it didn’t take much to realize that one of my favorite features in CakePHP just stopped working. When I asked on irc, gwoo said it doesn’t work.

I needed those and I wanted to avoid having to use the MySQL logging, so I started digging in the code until I got it fixed. So, if you’re like me, and want to optimize every single call made to your database, there is only one line to edit and that’s in /cake/console/cake.php at the bottom of the ShellDispatcher::__bootstrap() method:

//remove this line
Configure::write('debug', 1);

For some reason, they just decided to overwrite the debug settings you made in your core.php - I think someone had it there and just forgot about it because it’s supposed to be showing those errors. If you look at DbSource::showLog(), you will notice that it already takes care of separating between queries made using the command line (shells) and the ones using the browser (http) to show lines instead of table rows…

I really can’t answer what’s the reason for overwriting my debug settings but so far, removing it, hasn’t affected anything else, so I’ll consider it fixed for now. You have any idea why it’s there?

My last SQL optimization post found no response from the CakePHP community; which makes me wonder how many are actually using ACL with large databases. Anyhow, the ticket was submitted and gwoo seemed to approve the results. I can also now confirm that the last suggested indexes will give another push to your performance:

ARO: alias ACO: model and foreign_key

Now what’s that about HABTM? While reading the source code for the Model class, I stumbled on the Model::_deleteLinks() method:

function _deleteLinks($id) {
   $db =& ConnectionManager::getDataSource($this->useDbConfig);
   foreach ($this->hasAndBelongsToMany as $assoc => $data) {
      if (isset($data['with'])) {
         $model =& $this->{$data['with']};
         $records = $model->findAll(array($data['foreignKey'] => $id), $model->primaryKey, null, null, null, -1);


     if (!empty($records)) {
        foreach ($records as $record) {
           $model->delete($record[$model->name][$model->primaryKey]);
        }
     }
  } else {
     $table = $db->name($db->fullTableName($data['joinTable']));
     $conditions = $db->name($data['foreignKey']) . ' = ' . $db->value($id);
     $db->query("DELETE FROM {$table} WHERE {$conditions}");
  }


}
}

(more…)

Are you concerned about how fast your queries are handled? Do you run a website with lots of AROs & ACOs? If you answered no to any of the previous questions, you can just skip reading because it’s not for you.

Ok - so let’s dive directly in, I am short on time but started liking this sharing knowledge stuff.

Right now, whenever DB_ACL::check() kicks in (whenever a user needs to get authorized for an action), there is 2 main SELECT queries and then, depending on the number of nodes in the path to get to that user’s group, there is another query for each (so a minimum of one). When I was running some tests (debug=2) today, I just couldn’t ignore the number of queries made on each page refresh, etc. (more…)

Even though CakePHP’s model already includes many of the database query functions, I found that the SELECT DISTINCT was missing. Ok, I know that you can always do it using either Model->query('SELECT DISTINCT c1, c2) or Model->findAll(null, 'DISTINCT c1, c2‘) but that would be like saying use Model->query() instead of Model->findAll().

The cool thing in CakePHP is that you can add your own functions to use in your app on top of the ones that come bundled in the core. For the Model, you just create an ‘app_model.php’ file that you place in our app’s main folder. The empty file should look like this:

/**
 * Custom AppModel that adds functionality to the core Model
 */
class AppModel extends Model
{
   //empty
}

Now inside your new AppModel class, add the following function:

   /**
    * Returns a resultset array with DISTINCT fields from database matching given conditions.
    *
    * @param   mixed    $conditions SQL conditions as a string or as an array('field' =>'value',...)
    * @param   mixed    $fields Either a single string of a field name, or an array of field names
    * @return  array    Array of records
    */
   function findDistinct($conditions = null, $fields = null)
   {
      $db =& ConnectionManager::getDataSource($this->useDbConfig);


  $str = 'DISTINCT ';
  if (!is_array($fields))
  {
     $str .= '`' . $fields . '`';
  }
  else
  {
     foreach ($fields as $field)
     {
        $str .= '`' . $field . '`, ';
     }
     $str = substr($str, 0, -2);   
  }

  $queryData = array(
                 'conditions'   => $conditions,
                 'fields'       => $str,
                 );
  $data = $db->read($this, $queryData, false);

  return $data;      


}

You can now use Model->findDistinct('c1') or Model->findDistinct(array('c1', 'c2', 'c3')) to retrieve DISTINCT columns values.

Hoping you find it useful.

I’ve been really vague about the app we’re building here but that’s because I know it’s something that many in the affiliate marketing industry are trying to do right now and I prefer keeping everything like that until pre-launch.

Since 90% of what it does is data tracking and analysis from hundreds of sites. You can imagine now all kinds of settings and tables involved, all the database complexity. To start off, I identified groups based on similarities found at those sites. I then eliminated all the parts that could be just cloned and slightly modified to be added later on. All I had left was the strict minimum for the app to function and be able to run an example of each use case I had already written.

Time to design the database, identify the relations, etc. Here’s a scaled down version of what I came up with after an hour on PHPDesigner DBDesigner 4. I am sure certain things will get added or modified as we go but I believe this is a very solid start.