Create XML Sitemap for Large Sites: Dynamic Generation and Optimization
Moderate 18 min 2026-03-20

Create XML Sitemap for Large Sites: Dynamic Generation and Optimization

Quick Summary

  • What this covers: Build scalable XML sitemaps for sites with 100K+ pages. Implement dynamic sitemap generation, index files, and lastmod automation for crawl efficiency.
  • Who it's for: site owners and SEO practitioners
  • Key takeaway: Read the first section for the core framework, then use the specific tactics that match your situation.

XML sitemaps listing 50,000+ URLs hit protocol limits, consume excessive memory during generation, and overwhelm Googlebot with undifferentiated crawl targets. Large e-commerce sites, classified ad platforms, and content networks require sitemap index files splitting URLs by content type, update frequency, and priority — plus dynamic generation tied to database timestamps to avoid stale lastmod dates. Sites exceeding 50,000 URLs face specific technical constraints: the XML sitemap protocol caps individual sitemaps at 50,000 URLs and 50MB uncompressed, while Google Search Console processes up to 50,000 sitemap index files per property. This guide architects sitemap systems for sites scaling from 100,000 to 10 million+ pages using database-driven generation, compression, and automated submission.

XML Sitemap Protocol Limits for Large Sites

Sitemap protocol constraints:

Implications for large sites:

Phase 1: Design Sitemap Architecture

Segmentation strategy determines crawl efficiency and maintenance complexity.

Strategy 1: Segment by Content Type

Split sitemaps by content taxonomy — products, blog posts, categories, user profiles.

Example structure:

sitemap-index.xml
  ├── sitemap-products-1.xml (50,000 products)
  ├── sitemap-products-2.xml (50,000 products)
  ├── sitemap-blog.xml (15,000 posts)
  ├── sitemap-categories.xml (2,000 categories)
  └── sitemap-pages.xml (500 static pages)

Benefits:

Implementation pattern:

<!-- sitemap-index.xml -->
<?xml version="1.0" encoding="UTF-8"?>
<sitemapindex xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
  <sitemap>
    <loc>https://example.com/sitemap-products-1.xml.gz</loc>
    <lastmod>2026-02-08T10:30:00+00:00</lastmod>
  </sitemap>
  <sitemap>
    <loc>https://example.com/sitemap-products-2.xml.gz</loc>
    <lastmod>2026-02-08T10:30:00+00:00</lastmod>
  </sitemap>
  <sitemap>
    <loc>https://example.com/sitemap-blog.xml.gz</loc>
    <lastmod>2026-02-08T09:15:00+00:00</lastmod>
  </sitemap>
</sitemapindex>

Strategy 2: Segment by Update Frequency

Separate frequently updated content (daily inventory changes) from evergreen content (archived blog posts).

Example structure:

sitemap-index.xml
  ├── sitemap-realtime.xml (products added today)
  ├── sitemap-daily.xml (products updated in last 7 days)
  ├── sitemap-weekly.xml (products updated in last 30 days)
  └── sitemap-archive.xml (products older than 30 days)

Benefits:

Strategy 3: Hybrid (Content Type + Update Frequency)

Combines both strategies for maximum granularity.

Example structure:

sitemap-index.xml
  ├── sitemap-products-new.xml (new products, updated daily)
  ├── sitemap-products-1.xml (50,000 evergreen products)
  ├── sitemap-products-2.xml (50,000 evergreen products)
  ├── sitemap-blog-recent.xml (posts from last 30 days)
  └── sitemap-blog-archive.xml (posts older than 30 days)

Phase 2: Generate Sitemaps Dynamically from Database

Static sitemaps require manual regeneration and contain stale lastmod dates. Dynamic sitemaps query the database on-demand.

Dynamic Sitemap Generation (PHP Example)

Serve sitemap directly from database:

<?php
// sitemap-products-1.php
header('Content-Type: application/xml; charset=utf-8');

// Database connection
$pdo = new PDO('mysql:host=localhost;dbname=store', 'user', 'pass');

// Query first 50,000 products ordered by ID
$stmt = $pdo->prepare('
  SELECT url, updated_at, priority
  FROM products
  WHERE status = "published"
  ORDER BY id ASC
  LIMIT 50000
');
$stmt->execute();

echo '<?xml version="1.0" encoding="UTF-8"?>';
echo '<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">';

while ($row = $stmt->fetch()) {
  echo '<url>';
  echo '<loc>' . htmlspecialchars($row['url']) . '</loc>';
  echo '<lastmod>' . date('c', strtotime($row['updated_at'])) . '</lastmod>';
  echo '<changefreq>weekly</changefreq>';
  echo '<priority>' . $row['priority'] . '</priority>';
  echo '</url>';
}

echo '</urlset>';
?>

Advantages:

Disadvantages:

Cache Dynamic Sitemaps

Generate sitemap once, cache for 1 hour, serve cached version.

Caching strategy (Redis example):

<?php
$redis = new Redis();
$redis->connect('127.0.0.1', 6379);

$cacheKey = 'sitemap-products-1';
$sitemap = $redis->get($cacheKey);

if (!$sitemap) {
  // Generate sitemap (same query as above)
  $sitemap = generateSitemap(); // function containing DB query
  $redis->setex($cacheKey, 3600, $sitemap); // cache for 1 hour
}

header('Content-Type: application/xml; charset=utf-8');
echo $sitemap;
?>

Paginate Large Sitemaps

For content types exceeding 50,000 URLs, paginate sitemaps dynamically.

URL structure:

/sitemap-products.xml?page=1  (URLs 1-50,000)
/sitemap-products.xml?page=2  (URLs 50,001-100,000)
/sitemap-products.xml?page=3  (URLs 100,001-150,000)

Dynamic pagination (PHP):

<?php
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$perPage = 50000;
$offset = ($page - 1) * $perPage;

$stmt = $pdo->prepare('
  SELECT url, updated_at
  FROM products
  WHERE status = "published"
  ORDER BY id ASC
  LIMIT :limit OFFSET :offset
');
$stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();

// Output XML (same as above)
?>

Sitemap index references paginated sitemaps:

<sitemap>
  <loc>https://example.com/sitemap-products.xml?page=1</loc>
</sitemap>
<sitemap>
  <loc>https://example.com/sitemap-products.xml?page=2</loc>
</sitemap>

Phase 3: Automate Lastmod Dates

Accurate lastmod dates help Google prioritize fresh content. Many sites set lastmod to current date for all URLs, wasting crawl budget.

Pull Lastmod from Database Timestamps

Database schema must track updates:

CREATE TABLE products (
  id INT PRIMARY KEY,
  url VARCHAR(255),
  created_at DATETIME,
  updated_at DATETIME,  -- updated on any field change
  INDEX idx_updated (updated_at)
);

Sitemap query uses updated_at:

SELECT url, updated_at
FROM products
WHERE status = 'published'
ORDER BY updated_at DESC
LIMIT 50000;

Handle Content Without Update Timestamps

Static pages (about, contact) rarely change. Use file modification time as fallback.

PHP example:

$lastmod = file_exists('about.html')
  ? date('c', filemtime('about.html'))
  : date('c', strtotime('2024-01-01'));

Omit Lastmod If Unknown

XML sitemap protocol allows omitting lastmod. If you can't reliably track updates, leave it out — Google will recrawl based on historical change patterns.

<url>
  <loc>https://example.com/page-without-known-update</loc>
  <!-- no lastmod tag -->
</url>

Phase 4: Compress Sitemaps with Gzip

Gzip compression reduces sitemap size by 90%, speeding up Googlebot downloads and reducing bandwidth costs.

Generate Gzipped Sitemaps

Static sitemap compression:

gzip -k sitemap-products.xml
# Creates sitemap-products.xml.gz

Dynamic sitemap compression (PHP):

<?php
ob_start('ob_gzhandler');
header('Content-Type: application/xml; charset=utf-8');
header('Content-Encoding: gzip');

// Output sitemap XML
echo '<?xml version="1.0" encoding="UTF-8"?>';
// ... rest of sitemap
?>

Reference Gzipped Sitemaps in Index

Sitemap index should reference .gz files:

<sitemap>
  <loc>https://example.com/sitemap-products-1.xml.gz</loc>
  <lastmod>2026-02-08T10:30:00+00:00</lastmod>
</sitemap>

Google automatically detects and decompresses gzipped sitemaps.

Phase 5: Exclude Low-Value Pages

Sitemap bloat wastes crawl budget on pages with low search demand.

Exclude Paginated Pages Beyond Page 3

Pagination (page 1, 2, 3...50) creates thousands of low-value URLs. Include only page 1 in sitemap; let rel=next/prev handle discovery.

Example:

<!-- Include only base category page -->
<url>
  <loc>https://example.com/category/widgets</loc>
</url>

<!-- Omit paginated variants -->
<!-- https://example.com/category/widgets?page=2 -->
<!-- https://example.com/category/widgets?page=3 -->

Exclude Faceted Navigation URLs

Faceted filters (?color=red&size=large) create combinatorial explosion. Use canonical tags to consolidate, and exclude filtered URLs from sitemap.

Include in sitemap:

<url>
  <loc>https://example.com/products</loc>
</url>

Exclude from sitemap:

https://example.com/products?color=red
https://example.com/products?size=large
https://example.com/products?color=red&size=large

Exclude Duplicate Content

If you have multiple URLs serving identical content (session IDs, tracking parameters), canonicalize and include only canonical URL in sitemap.

Canonical URL in sitemap:

<url>
  <loc>https://example.com/product/widget</loc>
</url>

Excluded variants:

https://example.com/product/widget?sessionid=abc123
https://example.com/product/widget?utm_source=email

Phase 6: Automate Sitemap Submission

Manual submission to Google Search Console doesn't scale. Automate with Indexing API or scheduled pings.

Submit Sitemap Index to Search Console

One-time setup:

  1. Google Search Console → Sitemaps → Add sitemap
  2. Enter sitemap-index.xml
  3. Submit

Google checks sitemap index daily and discovers child sitemaps automatically.

Ping Google When Sitemap Updates

HTTP GET request notifies Google of sitemap changes:

curl "https://www.google.com/ping?sitemap=https://example.com/sitemap-index.xml"

Automate with cron (after regenerating sitemap):

# Regenerate sitemap hourly, ping Google
0 * * * * /usr/bin/php /var/www/generate-sitemap.php && curl "https://www.google.com/ping?sitemap=https://example.com/sitemap-index.xml"

Use Google Indexing API for Real-Time Updates

Indexing API (for job postings, live events, urgent content) bypasses sitemap delays.

Example (Python):

from oauth2client.service_account import ServiceAccountCredentials
import httplib2

SCOPES = ["https://www.googleapis.com/auth/indexing"]
credentials = ServiceAccountCredentials.from_json_keyfile_name('service-account.json', scopes=SCOPES)
http = credentials.authorize(httplib2.Http())

ENDPOINT = "https://indexing.googleapis.com/v3/urlNotifications:publish"
content = {
  "url": "https://example.com/new-product",
  "type": "URL_UPDATED"
}

response, content = http.request(ENDPOINT, method="POST", body=json.dumps(content))

Limits: 200 URLs/day (quotas can be increased for verified publishers).

Phase 7: Monitor Sitemap Health in Search Console

Google Search Console → Sitemaps reports sitemap processing status.

Interpret Sitemap Status Codes

"Couldn't fetch" error:

Fix: Test sitemap URL directly in browser, check server logs for Googlebot access errors.

"XML parsing error":

Fix: Validate sitemap with XML validator:

xmllint --noout sitemap.xml

"Unsupported file format":

Fix: Check Content-Type header:

curl -I https://example.com/sitemap.xml
# Should return: Content-Type: application/xml

Track Discovery Rate

Search Console → Sitemaps shows "Discovered URLs" vs. "Indexed URLs".

Healthy ratio: >80% indexed Problem ratio: <50% indexed (indicates quality issues, not sitemap issues)

If discovery rate is high but indexing rate is low, investigate crawl budget optimization and content quality.

Advanced: Dynamic Sitemap Index Generation

For sites with unpredictable content volume, generate sitemap index dynamically.

Count Total URLs and Generate Index

Query database to count URLs:

<?php
$stmt = $pdo->query('SELECT COUNT(*) FROM products WHERE status = "published"');
$totalProducts = $stmt->fetchColumn();
$sitemapsNeeded = ceil($totalProducts / 50000);

header('Content-Type: application/xml; charset=utf-8');
echo '<?xml version="1.0" encoding="UTF-8"?>';
echo '<sitemapindex xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">';

for ($i = 1; $i <= $sitemapsNeeded; $i++) {
  echo '<sitemap>';
  echo '<loc>https://example.com/sitemap-products.xml?page=' . $i . '</loc>';
  echo '<lastmod>' . date('c') . '</lastmod>';
  echo '</sitemap>';
}

echo '</sitemapindex>';
?>

This approach scales infinitely — if your product count grows from 100,000 to 1 million, the sitemap index automatically generates 20 child sitemaps instead of 2.

Frequently Asked Questions

Should I include images in XML sitemaps for large sites?

Only if images are critical for search visibility (e-commerce product photos, stock photography sites). Image sitemaps add significant size — a 50,000 product sitemap with 5 images each becomes 250,000 entries. For large sites, use separate image sitemaps and prioritize products with unique, high-value imagery. See image sitemap specification.

How often should I regenerate sitemaps for a large site?

Real-time content (news, inventory): regenerate hourly or use dynamic sitemaps. Moderate updates (e-commerce with daily changes): regenerate daily. Slow-moving content (SaaS documentation): regenerate weekly. Use lastmod dates to signal change frequency to Google, not sitemap regeneration frequency. Regenerating without content changes wastes server resources.

Can I use sitemap index files to organize sitemaps hierarchically (nested indexes)?

No. XML sitemap protocol supports only one level of sitemap index files. A sitemap index can reference sitemap files, but sitemap files cannot reference other sitemap indexes. Structure must be flat: index → sitemaps → URLs. For massive sites (10M+ pages), consider dynamic sitemaps or multiple sitemap indexes per property.

Do sitemap priorities and changefreq affect rankings?

No. Google ignores priority and treats changefreq as a hint, not a directive. However, accurate lastmod dates DO affect crawl frequency — Google recrawls pages with recent lastmod more often. Focus on accurate lastmod, omit or use generic values for priority and changefreq.

Should I block sitemaps from being crawled as pages themselves?

No. Googlebot needs to fetch sitemaps. Don't add sitemaps to robots.txt disallow. However, you can add noindex meta tags to dynamically generated sitemap URLs if they're accessible as HTML pages (shouldn't be an issue if you set proper XML Content-Type headers). Sitemaps should return Content-Type: application/xml, not HTML, so they won't be indexed as pages.

When This Fix Isn't Your Priority

Skip this for now if:

This is one piece of the system.

Built by Victor Romo (@b2bvic) — I build AI memory systems for businesses.

See The Full System View Repo
← All Fixes