Back To Programming / Scripting / Coding Forum

MySQL Limits

  • joebert
  • Sledgehammer
  • Genius
  • No Avatar
  • Joined: Feb 10, 2004
  • Posts: 13455
  • Loc: Florida
  • Status: Offline

Post October 21st, 2009, 2:38 am

I finally got around to the part of my project that needs to do something like this yesterday. I just wanted to make a note of two ways I tried.

Here's the first attempt. It averages 1.6 seconds to generate the page on the test server.

PHP Code: [ Select ]
   $result = $db->query('SELECT category_id, label, description FROM ' . CATEGORIES_TABLE . ' WHERE soi = TRUE ORDER BY label ASC', MYSQLI_USE_RESULT);
   while($row = $result->fetch_object())
   {
      $c->categories[]  = $row;
   }
   $result->close();
 
   $sql = $db->prepare('
      SELECT wp.label, wp.path, wp.wallpaper_id
         FROM ' . CAT_WP_RELATIONS_TABLE . ' rel
            LEFT JOIN ' . WALLPAPERS_TABLE . ' wp
               ON rel.wallpaper_id = wp.wallpaper_id
         WHERE rel.category_id IN ((SELECT category_id FROM ' . CATEGORIES_TABLE . " WHERE ? IN(category_id, parent_id)))
      ORDER BY rel.insert_order DESC LIMIT {$config->options->index->new_wallpapers->count}"
   );
   if(!$sql){$page->messages .= new message($db->error);}
   else{$sql->bind_param('s', $c->category_id);}
   
   foreach($c->categories as &$c->category)
   {if(!$sql){continue;}
      $c->category->url = sprintf($config->options->url->rewrite_mode ? $config->options->url->category_mask : 'index.php?category_id=%1$s&page=%3$s',
         $c->category->category_id,
         string_utils::htmlentities($c->category->label),
         1
      );
      $c->category_id   = $c->category->category_id;
      $c->wallpapers = '';
      if($sql->execute())
      {
         $sql->bind_result($c->wallpaper->label, $c->wallpaper->path, $c->wallpaper->wallpaper_id);
         while($sql->fetch())
         {
  1.    $result = $db->query('SELECT category_id, label, description FROM ' . CATEGORIES_TABLE . ' WHERE soi = TRUE ORDER BY label ASC', MYSQLI_USE_RESULT);
  2.    while($row = $result->fetch_object())
  3.    {
  4.       $c->categories[]  = $row;
  5.    }
  6.    $result->close();
  7.  
  8.    $sql = $db->prepare('
  9.       SELECT wp.label, wp.path, wp.wallpaper_id
  10.          FROM ' . CAT_WP_RELATIONS_TABLE . ' rel
  11.             LEFT JOIN ' . WALLPAPERS_TABLE . ' wp
  12.                ON rel.wallpaper_id = wp.wallpaper_id
  13.          WHERE rel.category_id IN ((SELECT category_id FROM ' . CATEGORIES_TABLE . " WHERE ? IN(category_id, parent_id)))
  14.       ORDER BY rel.insert_order DESC LIMIT {$config->options->index->new_wallpapers->count}"
  15.    );
  16.    if(!$sql){$page->messages .= new message($db->error);}
  17.    else{$sql->bind_param('s', $c->category_id);}
  18.    
  19.    foreach($c->categories as &$c->category)
  20.    {if(!$sql){continue;}
  21.       $c->category->url = sprintf($config->options->url->rewrite_mode ? $config->options->url->category_mask : 'index.php?category_id=%1$s&page=%3$s',
  22.          $c->category->category_id,
  23.          string_utils::htmlentities($c->category->label),
  24.          1
  25.       );
  26.       $c->category_id   = $c->category->category_id;
  27.       $c->wallpapers = '';
  28.       if($sql->execute())
  29.       {
  30.          $sql->bind_result($c->wallpaper->label, $c->wallpaper->path, $c->wallpaper->wallpaper_id);
  31.          while($sql->fetch())
  32.          {


Then there's the second attempt, takes 0.19 seconds on average to generate the page.

PHP Code: [ Select ]
   $c->categories = array();
   
   $result = $db->query('SELECT category_id, label, description FROM ' . CATEGORIES_TABLE . ' WHERE soi = TRUE', MYSQLI_USE_RESULT);
   while($row = $result->fetch_object())
   {
      $row->sub_categories          = array($row->category_id => $row->category_id);
      $c->categories[$row->category_id]   = $row;
   }
   $result->close();
   
   $result = $db->query('
      SELECT category_id, parent_id
         FROM ' . CATEGORIES_TABLE . '
         WHERE parent_id IN(' . implode(',', array_keys($c->categories)) . ')'
   );
   while($row = $result->fetch_object())
   {
      $c->categories[$row->parent_id]->sub_categories[$row->category_id] = $row->category_id;
   }
   $result->close();
   
   $c->max_subs = 0;
   foreach($c->categories as &$c->category)
   {
      $c->max_subs = max($c->max_subs, count($c->category->sub_categories));
   }
   foreach($c->categories as &$c->category)
   {
      $c->category->sub_categories = array_pad($c->category->sub_categories, $c->max_subs, current($c->category->sub_categories));
   }
   
   function __sort_cats_label($a, $b){return mb_strtolower($a->label) > mb_strtolower($b->label) ? 1 : -1;}
   usort($c->categories, '__sort_cats_label');
 
   $sql = $db->prepare('
      SELECT wp.label, wp.path, wp.wallpaper_id
         FROM ' . CAT_WP_RELATIONS_TABLE . ' rel
            LEFT JOIN ' . WALLPAPERS_TABLE . ' wp
               ON rel.wallpaper_id = wp.wallpaper_id
         WHERE rel.category_id IN (' . implode(',', array_fill(0, $c->max_subs, '?')) . ")
      ORDER BY rel.insert_order DESC LIMIT {$config->options->index->new_wallpapers->count}"
   );
   if(!$sql){$page->messages .= new message($db->error);}
   
   foreach($c->categories as &$c->category)
   {if(!$sql){continue;}
      $c->category->url = sprintf($config->options->url->rewrite_mode ? $config->options->url->category_mask : 'index.php?category_id=%1$s&page=%3$s',
         $c->category->category_id,
         string_utils::htmlentities($c->category->label),
         1
      );
      array_unshift($c->category->sub_categories, str_repeat('i', $c->max_subs));
      call_user_func_array(array($sql, 'bind_param'), $c->category->sub_categories);
      $c->wallpapers = '';
      if($sql->execute())
      {
         $sql->bind_result($c->wallpaper->label, $c->wallpaper->path, $c->wallpaper->wallpaper_id);
         while($sql->fetch())
         {
  1.    $c->categories = array();
  2.    
  3.    $result = $db->query('SELECT category_id, label, description FROM ' . CATEGORIES_TABLE . ' WHERE soi = TRUE', MYSQLI_USE_RESULT);
  4.    while($row = $result->fetch_object())
  5.    {
  6.       $row->sub_categories          = array($row->category_id => $row->category_id);
  7.       $c->categories[$row->category_id]   = $row;
  8.    }
  9.    $result->close();
  10.    
  11.    $result = $db->query('
  12.       SELECT category_id, parent_id
  13.          FROM ' . CATEGORIES_TABLE . '
  14.          WHERE parent_id IN(' . implode(',', array_keys($c->categories)) . ')'
  15.    );
  16.    while($row = $result->fetch_object())
  17.    {
  18.       $c->categories[$row->parent_id]->sub_categories[$row->category_id] = $row->category_id;
  19.    }
  20.    $result->close();
  21.    
  22.    $c->max_subs = 0;
  23.    foreach($c->categories as &$c->category)
  24.    {
  25.       $c->max_subs = max($c->max_subs, count($c->category->sub_categories));
  26.    }
  27.    foreach($c->categories as &$c->category)
  28.    {
  29.       $c->category->sub_categories = array_pad($c->category->sub_categories, $c->max_subs, current($c->category->sub_categories));
  30.    }
  31.    
  32.    function __sort_cats_label($a, $b){return mb_strtolower($a->label) > mb_strtolower($b->label) ? 1 : -1;}
  33.    usort($c->categories, '__sort_cats_label');
  34.  
  35.    $sql = $db->prepare('
  36.       SELECT wp.label, wp.path, wp.wallpaper_id
  37.          FROM ' . CAT_WP_RELATIONS_TABLE . ' rel
  38.             LEFT JOIN ' . WALLPAPERS_TABLE . ' wp
  39.                ON rel.wallpaper_id = wp.wallpaper_id
  40.          WHERE rel.category_id IN (' . implode(',', array_fill(0, $c->max_subs, '?')) . ")
  41.       ORDER BY rel.insert_order DESC LIMIT {$config->options->index->new_wallpapers->count}"
  42.    );
  43.    if(!$sql){$page->messages .= new message($db->error);}
  44.    
  45.    foreach($c->categories as &$c->category)
  46.    {if(!$sql){continue;}
  47.       $c->category->url = sprintf($config->options->url->rewrite_mode ? $config->options->url->category_mask : 'index.php?category_id=%1$s&page=%3$s',
  48.          $c->category->category_id,
  49.          string_utils::htmlentities($c->category->label),
  50.          1
  51.       );
  52.       array_unshift($c->category->sub_categories, str_repeat('i', $c->max_subs));
  53.       call_user_func_array(array($sql, 'bind_param'), $c->category->sub_categories);
  54.       $c->wallpapers = '';
  55.       if($sql->execute())
  56.       {
  57.          $sql->bind_result($c->wallpaper->label, $c->wallpaper->path, $c->wallpaper->wallpaper_id);
  58.          while($sql->fetch())
  59.          {


In both situations it's generating a page for 15 categories total, a couple are parent categories that include 1-10 sub categories.
I'm thinking about adding two columns to the categories table called "min_id" and "max"id" that line up with the insert_id column on my relations table that I can use with a BETWEEN clause to cut down on the number of rows it needs to scan. I have to work out how to handle updating those indexes though.
Strong with this one, the sudo is.
  • Anonymous
  • Bot
  • No Avatar
  • Joined: 25 Feb 2008
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post October 21st, 2009, 2:38 am

1, 2

Post Information

  • Total Posts in this topic: 16 posts
  • Users browsing this forum: No registered users and 153 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
 
 

© 2011 Unmelted, LLC. Ozzu® is a registered trademark of Unmelted, LLC.