For big magento stores, this is always a problem. One of my clients has more than 15 000 of configurable products on a multistore (7 stores) magento platform, to each of the configurable product is connected at least 3 simple products, that are set to be NOT shown individually.

This creates a LOT of url’s in core_url_rewrite which are not needed and are slowing down the store itself and may result in reaaaallly big table.
I have found on stackoverflow the “partially” solution to this problem, you can check it here, altough the answer provided there, will not work properly, but the idea itself was ok.

The solution – part 1

what we will do is change the refreshProductRewrites function of Mage_Catalog_Model_Url model (and for people who loves “extending” magento models, should write the extension for model, but this is not necessary – i will write an article about that someday).
you can find it in app/code/core/Mage/Catalog/Model/Url.php and original looks like that:

public function refreshProductRewrite($productId, $storeId = null)
{
    if (is_null($storeId)) {
        foreach ($this->getStores() as $store) {
            $this->refreshProductRewrite($productId, $store->getId());
        }
        return $this;
    }

    $product = $this->getResource()->getProduct($productId, $storeId);
    if ($product) {
        $store = $this->getStores($storeId);
        $storeRootCategoryId = $store->getRootCategoryId();

        // List of categories the product is assigned to, filtered by being within the store's categories root
        $categories = $this->getResource()->getCategories($product->getCategoryIds(), $storeId);
        $this->_rewrites = $this->getResource()->prepareRewrites($storeId, '', $productId);

        // Add rewrites for all needed categories
        // If product is assigned to any of store's categories -
        // we also should use store root category to create root product url rewrite
        if (!isset($categories[$storeRootCategoryId])) {
            $categories[$storeRootCategoryId] = $this->getResource()->getCategory($storeRootCategoryId, $storeId);
        }

        // Create product url rewrites
        foreach ($categories as $category) {
            $this->_refreshProductRewrite($product, $category);
        }

        // Remove all other product rewrites created earlier for this store - they're invalid now
        $excludeCategoryIds = array_keys($categories);
        $this->getResource()->clearProductRewrites($productId, $storeId, $excludeCategoryIds);

        unset($categories);
        unset($product);
    } else {
        // Product doesn't belong to this store - clear all its url rewrites including root one
        $this->getResource()->clearProductRewrites($productId, $storeId, array());
    }

    return $this;
}

The modified version of the code (exclude simple,not visible products):

public function refreshProductRewrites($storeId)
{
  $this->_categories      = array();
  $storeRootCategoryId    = $this->getStores($storeId)->getRootCategoryId();
  $storeRootCategoryPath  = $this->getStores($storeId)->getRootCategoryPath();
  $this->_categories[$storeRootCategoryId] = $this->getResource()->getCategory($storeRootCategoryId, $storeId);

  $lastEntityId = 0;
  $process = true;

  while ($process == true) {
      $products = $this->getResource()->getProductsByStore($storeId, $lastEntityId);
      if (!$products) {
          $process = false;
          break;
      }

      $this->_rewrites = $this->getResource()->prepareRewrites($storeId, false, array_keys($products));

      $loadCategories = array();
      foreach ($products as $product) {
          foreach ($product->getCategoryIds() as $categoryId) {
              if (!isset($this->_categories[$categoryId])) {
                  $loadCategories[$categoryId] = $categoryId;
              }
          }
      }

      if ($loadCategories) {
          foreach ($this->getResource()->getCategories($loadCategories, $storeId) as $category) {
              $this->_categories[$category->getId()] = $category;
          }
      }

      foreach ($products as $product) 
      {
        /*
          To distinguish between simple and other types of products, 
          and to NOT create any url rewrite for simple products, 
          which are invisible
        */
        $prod = Mage::getModel('catalog/product') -> load($product -> getId());
        $productType =  $prod -> getTypeID();
        $productVisibility = $prod -> getVisibility();
        if($productType === "simple" && $productVisibility === "1")
        {
          continue;
        }
        else
        {
          $this->_refreshProductRewrite($product, $this->_categories[$storeRootCategoryId]);
          foreach ($product->getCategoryIds() as $categoryId) 
          {
            if ($categoryId != $storeRootCategoryId && isset($this->_categories[$categoryId])) 
            {
              if (strpos($this->_categories[$categoryId]['path'], $storeRootCategoryPath . '/') !== 0) 
              {
                continue;
              }
              $this->_refreshProductRewrite($product, $this->_categories[$categoryId]);
            }
          }
        }
      }
      unset($products);
      $this->_rewrites = array();
}

The solution – part 2

This part of solution is dependent on the performance capability of your server AND how much products you have, you can either:

1. truncate refreshProductRewrites table and reindex Catalog URL Rewrites
2. manually clean the url database from simple products url’s – this should be fairly easy, if you create a simple products FROM configurable products with proper variables, without changing the name for simple products.

Effect

Before this operation my client had around 400 000 of urls in “URL Rewrite Management”, after the code change and some database cleaning this number went to around 130 000 of urls, which also increased speed of magento on a noticable level for the user (finding product and rendering time for the page was shorter)