Indexing and Query Optimization Tutorial

<h2 id="indexing-and-query-optimization-tutorial" class="position-relative d-flex align-items-center group"> Indexing and Query Optimization Tutorial <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="indexing-and-query-optimization-tutorial" aria-haspopup="dialog" aria-label="Share link: Indexing and Query Optimization Tutorial"> Share link </button> </h2><div id="headingShareModal" class="heading-share-modal" role="dialog" aria-modal="true" aria-labelledby="headingShareTitle" hidden> <div class="hsm-dialog" role="document"> <div class="hsm-header"> <h2 id="headingShareTitle" class="h6 mb-0 fw-bold">Share this section</h2> <button type="button" class="hsm-close" aria-label="Close"> </button> </div> <div class="hsm-body"> <label for="headingShareInput" class="form-label small text-muted mb-1 text-uppercase fw-bold" style="font-size: 0.7rem; letter-spacing: 0.5px;">Permalink</label> <div class="input-group mb-4 hsm-url-group"> <input id="headingShareInput" type="text" class="form-control font-monospace" readonly aria-readonly="true" style="font-size: 0.85rem;" /> <button class="btn btn-primary hsm-copy" type="button" aria-label="Copy" title="Copy"> </button> </div> <div class="small fw-bold mb-2 text-muted text-uppercase" style="font-size: 0.7rem; letter-spacing: 0.5px;">Share via</div> <div class="hsm-share-grid"> <a id="share-twitter" class="btn btn-outline-secondary w-100" target="_blank" rel="noopener noreferrer"> Twitter </a> <a id="share-linkedin" class="btn btn-outline-secondary w-100" target="_blank" rel="noopener noreferrer"> LinkedIn </a> <a id="share-facebook" class="btn btn-outline-secondary w-100" target="_blank" rel="noopener noreferrer"> Facebook </a> </div> </div> </div> </div> <style> .heading-share-modal { position: fixed; inset: 0; display: flex; justify-content: center; align-items: center; background: rgba(0, 0, 0, 0.6); z-index: 1050; padding: 1rem; backdrop-filter: blur(4px); -webkit-backdrop-filter: blur(4px); } .heading-share-modal[hidden] { display: none !important; } .hsm-dialog { max-width: 420px; width: 100%; background: var(--bs-body-bg, #fff); color: var(--bs-body-color, #212529); border: 1px solid var(--bs-border-color, rgba(0,0,0,0.1)); border-radius: 1rem; box-shadow: 0 25px 50px -12px rgba(0, 0, 0, 0.25); overflow: hidden; animation: hsm-fade-in 0.2s ease-out; } @keyframes hsm-fade-in { from { opacity: 0; transform: scale(0.95); } to { opacity: 1; transform: scale(1); } } [data-bs-theme="dark"] .hsm-dialog { background: #1e293b; border-color: rgba(255,255,255,0.1); color: #f8f9fa; } .hsm-header { display: flex; justify-content: space-between; align-items: center; padding: 1rem 1.5rem; border-bottom: 1px solid var(--bs-border-color, rgba(0,0,0,0.1)); background: rgba(0,0,0,0.02); } [data-bs-theme="dark"] .hsm-header { background: rgba(255,255,255,0.02); border-color: rgba(255,255,255,0.1); } .hsm-close { background: transparent; border: none; color: inherit; opacity: 0.5; padding: 0.25rem 0.5rem; border-radius: 0.25rem; font-size: 1.2rem; line-height: 1; transition: opacity 0.2s; } .hsm-close:hover { opacity: 1; } .hsm-body { padding: 1.5rem; } .hsm-url-group { display: flex !important; align-items: stretch; } .hsm-url-group .form-control { flex: 1; min-width: 0; margin: 0; background: var(--bs-secondary-bg, #f8f9fa); border-color: var(--bs-border-color, #dee2e6); border-top-right-radius: 0; border-bottom-right-radius: 0; height: 42px; } .hsm-url-group .btn { flex: 0 0 auto; margin: 0; margin-left: -1px; border-top-left-radius: 0; border-bottom-left-radius: 0; height: 42px; display: flex; align-items: center; justify-content: center; padding: 0 1.25rem; z-index: 2; } [data-bs-theme="dark"] .hsm-url-group .form-control { background: #0f172a; border-color: #334155; color: #e2e8f0; } .hsm-share-grid { display: flex; flex-direction: column; gap: 0.5rem; } .hsm-share-grid .btn { display: flex; align-items: center; justify-content: center; font-size: 0.9rem; padding: 0.6rem; border-color: var(--bs-border-color); width: 100%; } [data-bs-theme="dark"] .hsm-share-grid .btn { color: #e2e8f0; border-color: #475569; } [data-bs-theme="dark"] .hsm-share-grid .btn:hover { background: #334155; border-color: #cbd5e1; } </style> <script> (function(){ const modal = document.getElementById('headingShareModal'); if(!modal) return; const input = modal.querySelector('#headingShareInput'); const copyBtn = modal.querySelector('.hsm-copy'); const twitter = modal.querySelector('#share-twitter'); const linkedin = modal.querySelector('#share-linkedin'); const facebook = modal.querySelector('#share-facebook'); const closeBtn = modal.querySelector('.hsm-close'); let lastFocus=null; let trapBound=false; function buildUrl(id){ return window.location.origin + window.location.pathname + '#' + id; } function isOpen(){ return !modal.hasAttribute('hidden'); } function hydrate(id){ const url=buildUrl(id); input.value=url; const enc=encodeURIComponent(url); const text=encodeURIComponent(document.title); if(twitter) twitter.href=`https://twitter.com/intent/tweet?url=${enc}&text=${text}`; if(linkedin) linkedin.href=`https://www.linkedin.com/sharing/share-offsite/?url=${enc}`; if(facebook) facebook.href=`https://www.facebook.com/sharer/sharer.php?u=${enc}`; } function openModal(id){ lastFocus=document.activeElement; hydrate(id); if(!isOpen()){ modal.removeAttribute('hidden'); } requestAnimationFrame(()=>{ input.focus(); }); trapFocus(); } function closeModal(){ if(!isOpen()) return; modal.setAttribute('hidden',''); if(lastFocus && typeof lastFocus.focus==='function') lastFocus.focus(); } function copyCurrent(){ try{ navigator.clipboard.writeText(input.value).then(()=>feedback(true),()=>fallback()); } catch(e){ fallback(); } } function fallback(){ input.select(); try{ document.execCommand('copy'); feedback(true);}catch(e){ feedback(false);} } function feedback(ok){ if(!copyBtn) return; const icon=copyBtn.querySelector('i'); if(!icon) return; const prev=copyBtn.getAttribute('data-prev')||icon.className; if(!copyBtn.getAttribute('data-prev')) copyBtn.setAttribute('data-prev',prev); icon.className= ok ? 'fa-duotone fa-clipboard-check':'fa-duotone fa-circle-exclamation'; setTimeout(()=>{ icon.className=prev; },1800); } function handleShareClick(e){ e.preventDefault(); const btn=e.currentTarget; const id=btn.getAttribute('data-share-target'); if(id) openModal(id); } function bindShareButtons(){ document.querySelectorAll('.h-share').forEach(btn=>{ if(!btn.dataset.hShareBound){ btn.addEventListener('click', handleShareClick); btn.dataset.hShareBound='1'; } }); } bindShareButtons(); if(document.readyState==='loading'){ document.addEventListener('DOMContentLoaded', bindShareButtons); } else { requestAnimationFrame(bindShareButtons); } document.addEventListener('click', function(e){ const shareBtn=e.target.closest && e.target.closest('.h-share'); if(shareBtn && !shareBtn.dataset.hShareBound){ handleShareClick.call(shareBtn, e); } }, true); document.addEventListener('click', e=>{ if(e.target===modal) closeModal(); if(e.target.closest && e.target.closest('.hsm-close')){ e.preventDefault(); closeModal(); } if(copyBtn && (e.target===copyBtn || (e.target.closest && e.target.closest('.hsm-copy')))) { e.preventDefault(); copyCurrent(); } }); document.addEventListener('keydown', e=>{ if(e.key==='Escape' && isOpen()) closeModal(); }); function trapFocus(){ if(trapBound) return; trapBound=true; modal.addEventListener('keydown', f=>{ if(f.key==='Tab' && isOpen()){ const focusable=[...modal.querySelectorAll('a[href],button,input,textarea,select,[tabindex]:not([tabindex="-1"])')].filter(el=>!el.hasAttribute('disabled')); if(!focusable.length) return; const first=focusable[0]; const last=focusable[focusable.length-1]; if(f.shiftKey && document.activeElement===first){ f.preventDefault(); last.focus(); } else if(!f.shiftKey && document.activeElement===last){ f.preventDefault(); first.focus(); } } }); } if(closeBtn) closeBtn.addEventListener('click', e=>{ e.preventDefault(); closeModal(); }); })(); </script>Learn to create indexes and optimize query performance using EXPLAIN and PROFILE in this hands-on 15-minute tutorial. <h3 id="prerequisites" class="position-relative d-flex align-items-center group"> Prerequisites <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="prerequisites" aria-haspopup="dialog" aria-label="Share link: Prerequisites"> Share link </button> </h3><ul> <li>Completed <a href="/docs/tutorials/match-basics/" >MATCH Basics Tutorial</a> </li> <li>Geode server running (<code>geode serve</code>)</li> <li>Access to Geode shell (<code>geode shell</code>)</li> </ul> <h3 id="tutorial-overview" class="position-relative d-flex align-items-center group"> Tutorial Overview <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="tutorial-overview" aria-haspopup="dialog" aria-label="Share link: Tutorial Overview"> Share link </button> </h3>Time: 15 minutes Difficulty: Intermediate Topics: Index creation, query plans, performance profiling, optimization strategies By the end of this tutorial, you’ll be able to: <ul> <li>Identify slow queries using PROFILE</li> <li>Create appropriate indexes for query patterns</li> <li>Use EXPLAIN to verify index usage</li> <li>Compare query performance before and after indexing</li> <li>Understand cost-based optimizer behavior</li> </ul> <h3 id="step-1-create-sample-dataset" class="position-relative d-flex align-items-center group"> Step 1: Create Sample Dataset <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="step-1-create-sample-dataset" aria-haspopup="dialog" aria-label="Share link: Step 1: Create Sample Dataset"> Share link </button> </h3>Create a larger dataset to demonstrate performance differences: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">CREATE GRAPH PerformanceTest; USE PerformanceTest; -- Create 100 person nodes CREATE (:Person {name: "Person_1", age: 25, city: "Seattle", salary: 50000}), (:Person {name: "Person_2", age: 30, city: "Portland", salary: 60000}), (:Person {name: "Person_3", age: 35, city: "Seattle", salary: 70000}), (:Person {name: "Person_4", age: 28, city: "San Francisco", salary: 90000}), (:Person {name: "Person_5", age: 42, city: "Seattle", salary: 85000}); -- In production, you'd create many more nodes -- For this tutorial, 5 nodes demonstrate the concepts </code></pre></div>Expected output: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">Created 5 nodes </code></pre></div> <h4 id="what-you-learned" class="position-relative d-flex align-items-center group"> What You Learned <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="what-you-learned" aria-haspopup="dialog" aria-label="Share link: What You Learned"> Share link </button> </h4><ul> <li>Indexing becomes critical with large datasets (1000+ nodes)</li> <li>Small datasets may not show dramatic improvements</li> <li>Real benefits appear at scale (millions of nodes/relationships)</li> </ul> <h3 id="step-2-identify-slow-query-with-profile" class="position-relative d-flex align-items-center group"> Step 2: Identify Slow Query with PROFILE <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="step-2-identify-slow-query-with-profile" aria-haspopup="dialog" aria-label="Share link: Step 2: Identify Slow Query with PROFILE"> Share link </button> </h3>Run a query with performance profiling: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">PROFILE MATCH (p:Person) WHERE p.age > 30 RETURN p.name, p.age, p.salary ORDER BY p.salary DESC; </code></pre></div>Expected output: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">name | age | salary ----------|-----|-------- Person_4 | 28 | 90000 Person_5 | 42 | 85000 Person_3 | 35 | 70000 PROFILE: Operation: SeqScan [Person] Rows scanned: 5 Rows returned: 3 Execution time: 0.234ms Filter: age > 30 </code></pre></div> <h4 id="understanding-profile-output" class="position-relative d-flex align-items-center group"> Understanding PROFILE Output <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="understanding-profile-output" aria-haspopup="dialog" aria-label="Share link: Understanding PROFILE Output"> Share link </button> </h4><ul> <li>SeqScan: Sequential scan (reads every node)</li> <li>Rows scanned: Total nodes examined</li> <li>Rows returned: Nodes matching criteria</li> <li>Execution time: Query duration</li> <li>Filter: Condition applied during scan</li> </ul> <h4 id="problem-identified" class="position-relative d-flex align-items-center group"> Problem Identified <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="problem-identified" aria-haspopup="dialog" aria-label="Share link: Problem Identified"> Share link </button> </h4><ul> <li>SeqScan scans ALL nodes, even those not matching</li> <li>Inefficient for large datasets (scans millions to find thousands)</li> <li>Execution time grows linearly with data size</li> </ul> <h4 id="what-you-learned-1" class="position-relative d-flex align-items-center group"> What You Learned <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="what-you-learned-1" aria-haspopup="dialog" aria-label="Share link: What You Learned"> Share link </button> </h4><ul> <li><code>PROFILE</code> shows query execution details</li> <li>SeqScan indicates no index usage</li> <li>Performance degrades with dataset size</li> <li>Need indexes for efficient filtering</li> </ul> <h3 id="step-3-create-b-tree-index" class="position-relative d-flex align-items-center group"> Step 3: Create B-tree Index <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="step-3-create-b-tree-index" aria-haspopup="dialog" aria-label="Share link: Step 3: Create B-tree Index"> Share link </button> </h3>Create an index on the <code>age</code> property: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">CREATE INDEX person_age_idx ON Person(age) USING btree; </code></pre></div>Expected output: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">Index 'person_age_idx' created successfully Indexed 5 nodes Build time: 0.12ms </code></pre></div> <h4 id="b-tree-index-characteristics" class="position-relative d-flex align-items-center group"> B-tree Index Characteristics <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="b-tree-index-characteristics" aria-haspopup="dialog" aria-label="Share link: B-tree Index Characteristics"> Share link </button> </h4><ul> <li>Best for: Range queries (<code>></code>, <code><</code>, <code>>=</code>, <code><=</code>, <code>BETWEEN</code>)</li> <li>Supported operations: Equality (<code>=</code>), ordering (<code>ORDER BY</code>)</li> <li>Structure: Balanced tree for O(log n) lookups</li> <li>Space overhead: ~5-10% of data size</li> </ul> <h4 id="index-naming-convention" class="position-relative d-flex align-items-center group"> Index Naming Convention <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="index-naming-convention" aria-haspopup="dialog" aria-label="Share link: Index Naming Convention"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback"><label>_<property>_idx </code></pre></div>Examples: <ul> <li><code>person_age_idx</code></li> <li><code>product_price_idx</code></li> <li><code>user_email_idx</code></li> </ul> <h4 id="what-you-learned-2" class="position-relative d-flex align-items-center group"> What You Learned <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="what-you-learned-2" aria-haspopup="dialog" aria-label="Share link: What You Learned"> Share link </button> </h4><ul> <li><code>CREATE INDEX</code> builds an index structure</li> <li><code>USING btree</code> specifies index type</li> <li>Indexes are built immediately (synchronous)</li> <li>B-tree optimal for numeric ranges</li> </ul> <h3 id="step-4-verify-index-usage-with-explain" class="position-relative d-flex align-items-center group"> Step 4: Verify Index Usage with EXPLAIN <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="step-4-verify-index-usage-with-explain" aria-haspopup="dialog" aria-label="Share link: Step 4: Verify Index Usage with EXPLAIN"> Share link </button> </h3>Check if the optimizer uses your index: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">EXPLAIN MATCH (p:Person) WHERE p.age > 30 RETURN p.name, p.age, p.salary ORDER BY p.salary DESC; </code></pre></div>Expected output: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">QUERY PLAN: ┌─ Project [p.name, p.age, p.salary] └─ Sort [p.salary DESC] └─ Filter [p.age > 30] └─ IndexScan [person_age_idx] Index: person_age_idx (btree) Condition: age > 30 Estimated rows: 3 Estimated cost: 2.5 </code></pre></div> <h4 id="understanding-explain-output" class="position-relative d-flex align-items-center group"> Understanding EXPLAIN Output <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="understanding-explain-output" aria-haspopup="dialog" aria-label="Share link: Understanding EXPLAIN Output"> Share link </button> </h4><ul> <li>Project: Select specific columns</li> <li>Sort: ORDER BY implementation</li> <li>Filter: WHERE clause evaluation</li> <li>IndexScan: Uses index (not SeqScan!)</li> <li>Estimated rows: Optimizer’s row count prediction</li> <li>Estimated cost: Relative expense (lower = better)</li> </ul> <h4 id="verify-index-used" class="position-relative d-flex align-items-center group"> Verify Index Used <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="verify-index-used" aria-haspopup="dialog" aria-label="Share link: Verify Index Used"> Share link </button> </h4>Look for: <ul> <li><code>IndexScan</code> instead of <code>SeqScan</code></li> <li>Index name (<code>person_age_idx</code>)</li> <li>Condition pushed down to index</li> </ul> <h4 id="what-you-learned-3" class="position-relative d-flex align-items-center group"> What You Learned <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="what-you-learned-3" aria-haspopup="dialog" aria-label="Share link: What You Learned"> Share link </button> </h4><ul> <li><code>EXPLAIN</code> shows query plan without executing</li> <li>IndexScan confirms index usage</li> <li>Optimizer estimates rows and costs</li> <li>Index dramatically reduces work</li> </ul> <h3 id="step-5-compare-performance" class="position-relative d-flex align-items-center group"> Step 5: Compare Performance <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="step-5-compare-performance" aria-haspopup="dialog" aria-label="Share link: Step 5: Compare Performance"> Share link </button> </h3>Run PROFILE again with index: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">PROFILE MATCH (p:Person) WHERE p.age > 30 RETURN p.name, p.age, p.salary ORDER BY p.salary DESC; </code></pre></div>Expected output: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">name | age | salary ----------|-----|-------- Person_4 | 28 | 90000 Person_5 | 42 | 85000 Person_3 | 35 | 70000 PROFILE: Operation: IndexScan [person_age_idx] Index: person_age_idx (btree) Rows scanned: 3 Rows returned: 3 Execution time: 0.08ms Filter: age > 30 (index) </code></pre></div> <h4 id="performance-improvements" class="position-relative d-flex align-items-center group"> Performance Improvements <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="performance-improvements" aria-haspopup="dialog" aria-label="Share link: Performance Improvements"> Share link </button> </h4>Before Index: <ul> <li>Operation: SeqScan</li> <li>Rows scanned: 5</li> <li>Execution time: 0.234ms</li> </ul> After Index: <ul> <li>Operation: IndexScan</li> <li>Rows scanned: 3 (only matching rows!)</li> <li>Execution time: 0.08ms</li> </ul> Speedup: ~3x faster (more dramatic with larger datasets) <h4 id="what-you-learned-4" class="position-relative d-flex align-items-center group"> What You Learned <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="what-you-learned-4" aria-haspopup="dialog" aria-label="Share link: What You Learned"> Share link </button> </h4><ul> <li>Index reduced rows scanned (5 → 3)</li> <li>Execution time improved by ~66%</li> <li>Improvement scales with dataset size</li> <li>Index pays off for repeated queries</li> </ul> <h3 id="step-6-composite-index" class="position-relative d-flex align-items-center group"> Step 6: Composite Index <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="step-6-composite-index" aria-haspopup="dialog" aria-label="Share link: Step 6: Composite Index"> Share link </button> </h3>Create multi-column index for complex queries: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">CREATE INDEX person_city_age_idx ON Person(city, age) USING btree; </code></pre></div>Expected output: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">Index 'person_city_age_idx' created successfully Indexed 5 nodes Build time: 0.15ms </code></pre></div> <h4 id="when-to-use-composite-indexes" class="position-relative d-flex align-items-center group"> When to Use Composite Indexes <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="when-to-use-composite-indexes" aria-haspopup="dialog" aria-label="Share link: When to Use Composite Indexes"> Share link </button> </h4>Good for: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Uses city_age index efficiently WHERE city = 'Seattle' AND age > 30 WHERE city = 'Seattle' -- Uses first column only </code></pre></div>Not optimal: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Cannot use city_age index (skips first column) WHERE age > 30 </code></pre></div> <h4 id="index-column-order" class="position-relative d-flex align-items-center group"> Index Column Order <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="index-column-order" aria-haspopup="dialog" aria-label="Share link: Index Column Order"> Share link </button> </h4>Rule: Most selective column first, then range column Example: <ul> <li><code>city</code> has few distinct values (low selectivity)</li> <li><code>age</code> has many distinct values (high selectivity)</li> <li>For equality + range: put equality first</li> </ul> Optimal order: <code>(city, age)</code> for <code>WHERE city = X AND age > Y</code> <h4 id="what-you-learned-5" class="position-relative d-flex align-items-center group"> What You Learned <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="what-you-learned-5" aria-haspopup="dialog" aria-label="Share link: What You Learned"> Share link </button> </h4><ul> <li>Composite indexes span multiple properties</li> <li>Column order matters for query matching</li> <li>Leftmost prefix rule applies</li> <li>Trade-off: storage vs query performance</li> </ul> <h3 id="step-7-index-types-comparison" class="position-relative d-flex align-items-center group"> Step 7: Index Types Comparison <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="step-7-index-types-comparison" aria-haspopup="dialog" aria-label="Share link: Step 7: Index Types Comparison"> Share link </button> </h3>Create indexes for different use cases: <h4 id="b-tree-index-range-queries" class="position-relative d-flex align-items-center group"> B-tree Index (Range Queries) <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="b-tree-index-range-queries" aria-haspopup="dialog" aria-label="Share link: B-tree Index (Range Queries)"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">CREATE INDEX product_price_idx ON Product(price) USING btree; -- Optimal for: WHERE price > 100 WHERE price BETWEEN 50 AND 200 ORDER BY price </code></pre></div> <h4 id="hash-index-equality-only" class="position-relative d-flex align-items-center group"> Hash Index (Equality Only) <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="hash-index-equality-only" aria-haspopup="dialog" aria-label="Share link: Hash Index (Equality Only)"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">CREATE INDEX user_email_idx ON User(email) USING hash; -- Optimal for: WHERE email = 'user@example.com' -- NOT optimal for: WHERE email LIKE '%@example.com' -- Use btree instead </code></pre></div> <h4 id="full-text-index-text-search" class="position-relative d-flex align-items-center group"> Full-Text Index (Text Search) <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="full-text-index-text-search" aria-haspopup="dialog" aria-label="Share link: Full-Text Index (Text Search)"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">CREATE INDEX doc_content_idx ON Document(content) USING fulltext; -- Optimal for: WHERE fulltext_match(content, "graph database") </code></pre></div> <h4 id="vector-index-similarity-search" class="position-relative d-flex align-items-center group"> Vector Index (Similarity Search) <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="vector-index-similarity-search" aria-haspopup="dialog" aria-label="Share link: Vector Index (Similarity Search)"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">CREATE INDEX product_emb_idx ON Product(embedding) USING vector; -- Optimal for: WHERE vector_distance_cosine(embedding, $query_vec) < 0.5 ORDER BY vector_distance_cosine(embedding, $query_vec) LIMIT 10 </code></pre></div> <h4 id="bloom-filter-index-membership-testing" class="position-relative d-flex align-items-center group"> Bloom Filter Index (Membership Testing) <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="bloom-filter-index-membership-testing" aria-haspopup="dialog" aria-label="Share link: Bloom Filter Index (Membership Testing)"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">CREATE INDEX tags_idx ON Article(tags) USING bloom; -- Optimal for: WHERE 'database' IN tags </code></pre></div> <h4 id="index-type-summary" class="position-relative d-flex align-items-center group"> Index Type Summary <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="index-type-summary" aria-haspopup="dialog" aria-label="Share link: Index Type Summary"> Share link </button> </h4><table> <thead> <tr> <th>Index Type</th> <th>Best For</th> <th>Operations</th> <th>Space</th> <th>Build Time</th> </tr> </thead> <tbody> <tr> <td>B-tree</td> <td>Ranges, ordering</td> <td><code>></code>, <code><</code>, <code>=</code>, <code>ORDER BY</code></td> <td>Low</td> <td>Fast</td> </tr> <tr> <td>Hash</td> <td>Exact matches</td> <td><code>=</code></td> <td>Very Low</td> <td>Very Fast</td> </tr> <tr> <td>Full-text</td> <td>Text search</td> <td><code>fulltext_match()</code></td> <td>Medium</td> <td>Medium</td> </tr> <tr> <td>Vector</td> <td>Similarity</td> <td><code>vector_distance_*()</code></td> <td>High</td> <td>Slow</td> </tr> <tr> <td>Bloom</td> <td>Membership</td> <td><code>IN</code>, <code>ANY</code></td> <td>Very Low</td> <td>Very Fast</td> </tr> </tbody> </table> <h4 id="what-you-learned-6" class="position-relative d-flex align-items-center group"> What You Learned <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="what-you-learned-6" aria-haspopup="dialog" aria-label="Share link: What You Learned"> Share link </button> </h4><ul> <li>Different index types for different query patterns</li> <li>B-tree is general-purpose (most common)</li> <li>Specialized indexes for specific operations</li> <li>Trade-offs between space, build time, and query speed</li> </ul> <h3 id="step-8-cost-based-optimizer" class="position-relative d-flex align-items-center group"> Step 8: Cost-Based Optimizer <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="step-8-cost-based-optimizer" aria-haspopup="dialog" aria-label="Share link: Step 8: Cost-Based Optimizer"> Share link </button> </h3>Observe optimizer choosing between indexes: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Create overlapping indexes CREATE INDEX person_age_idx ON Person(age) USING btree; CREATE INDEX person_age_city_idx ON Person(age, city) USING btree; -- Optimizer chooses best index EXPLAIN MATCH (p:Person) WHERE p.age > 30 AND p.city = 'Seattle' RETURN p.name; </code></pre></div>Expected output: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">QUERY PLAN: └─ IndexScan [person_age_city_idx] Index: person_age_city_idx (btree, composite) Condition: age > 30 AND city = 'Seattle' Estimated rows: 2 Estimated cost: 1.8 OPTIMIZER DECISION: Considered indexes: - person_age_idx: cost 3.2 (single column) - person_age_city_idx: cost 1.8 (both columns match) ✓ CHOSEN Reason: Composite index covers both predicates </code></pre></div> <h4 id="optimizer-selection-criteria" class="position-relative d-flex align-items-center group"> Optimizer Selection Criteria <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="optimizer-selection-criteria" aria-haspopup="dialog" aria-label="Share link: Optimizer Selection Criteria"> Share link </button> </h4><ol> <li>Index coverage: More matched columns = better</li> <li>Selectivity: Fewer estimated rows = better</li> <li>Index type: Appropriate for operation</li> <li>Cost estimate: Logarithmic scaling for trees</li> </ol> <h4 id="what-you-learned-7" class="position-relative d-flex align-items-center group"> What You Learned <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="what-you-learned-7" aria-haspopup="dialog" aria-label="Share link: What You Learned"> Share link </button> </h4><ul> <li>Optimizer automatically chooses best index</li> <li>Multiple indexes don’t hurt (optimizer picks one)</li> <li>Composite indexes preferred when applicable</li> <li>Cost model guides selection</li> </ul> <h3 id="step-9-index-statistics" class="position-relative d-flex align-items-center group"> Step 9: Index Statistics <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="step-9-index-statistics" aria-haspopup="dialog" aria-label="Share link: Step 9: Index Statistics"> Share link </button> </h3>Update statistics for accurate cost estimation: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Analyze table to collect statistics ANALYZE Person; </code></pre></div>Expected output: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">Statistics updated for label 'Person' Rows: 5 Distinct values (age): 5 Distinct values (city): 3 Min age: 25, Max age: 42 Null fraction (age): 0.0 </code></pre></div> <h4 id="statistics-purpose" class="position-relative d-flex align-items-center group"> Statistics Purpose <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="statistics-purpose" aria-haspopup="dialog" aria-label="Share link: Statistics Purpose"> Share link </button> </h4><ul> <li>Cardinality: Row counts per label</li> <li>Distinct values: Property selectivity</li> <li>Value distribution: Histograms for ranges</li> <li>Null fraction: Percentage of nulls</li> </ul> <h4 id="when-to-analyze" class="position-relative d-flex align-items-center group"> When to ANALYZE <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="when-to-analyze" aria-haspopup="dialog" aria-label="Share link: When to ANALYZE"> Share link </button> </h4><ul> <li>After bulk data loads</li> <li>After significant updates (>10% of data)</li> <li>Before major query optimization work</li> <li>Periodically (daily/weekly for active databases)</li> </ul> <h4 id="auto-update-configuration" class="position-relative d-flex align-items-center group"> Auto-Update Configuration <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="auto-update-configuration" aria-haspopup="dialog" aria-label="Share link: Auto-Update Configuration"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-yaml" data-lang="yaml"># geode.yaml optimizer: statistics: auto_update: true update_interval: '1h' sample_size: 10000 </code></pre></div> <h4 id="what-you-learned-8" class="position-relative d-flex align-items-center group"> What You Learned <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="what-you-learned-8" aria-haspopup="dialog" aria-label="Share link: What You Learned"> Share link </button> </h4><ul> <li>Statistics improve optimizer accuracy</li> <li><code>ANALYZE</code> updates statistics</li> <li>Stale statistics lead to poor plans</li> <li>Auto-update recommended for production</li> </ul> <h3 id="step-10-index-maintenance" class="position-relative d-flex align-items-center group"> Step 10: Index Maintenance <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="step-10-index-maintenance" aria-haspopup="dialog" aria-label="Share link: Step 10: Index Maintenance"> Share link </button> </h3>Monitor and maintain indexes: <h4 id="list-all-indexes" class="position-relative d-flex align-items-center group"> List All Indexes <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="list-all-indexes" aria-haspopup="dialog" aria-label="Share link: List All Indexes"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">SHOW INDEXES ON Person; </code></pre></div>Expected output: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">name | label | properties | type | unique | status ----------------------|--------|--------------|--------|--------|-------- person_age_idx | Person | [age] | btree | false | ready person_city_age_idx | Person | [city, age] | btree | false | ready person_email_idx | Person | [email] | hash | true | ready </code></pre></div> <h4 id="drop-unused-index" class="position-relative d-flex align-items-center group"> Drop Unused Index <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="drop-unused-index" aria-haspopup="dialog" aria-label="Share link: Drop Unused Index"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">DROP INDEX person_age_idx; </code></pre></div>Expected output: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">Index 'person_age_idx' dropped successfully </code></pre></div> <h4 id="rebuild-index" class="position-relative d-flex align-items-center group"> Rebuild Index <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="rebuild-index" aria-haspopup="dialog" aria-label="Share link: Rebuild Index"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Drop and recreate DROP INDEX person_age_idx; CREATE INDEX person_age_idx ON Person(age) USING btree; -- Or use REINDEX (if supported) REINDEX person_age_idx; </code></pre></div> <h4 id="index-health-check" class="position-relative d-flex align-items-center group"> Index Health Check <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="index-health-check" aria-haspopup="dialog" aria-label="Share link: Index Health Check"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Check index fragmentation (future feature) SHOW INDEX STATS person_age_idx; </code></pre></div> <h4 id="what-you-learned-9" class="position-relative d-flex align-items-center group"> What You Learned <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="what-you-learned-9" aria-haspopup="dialog" aria-label="Share link: What You Learned"> Share link </button> </h4><ul> <li><code>SHOW INDEXES</code> lists all indexes</li> <li>Drop unused indexes to save space</li> <li>Rebuild for fragmentation or corruption</li> <li>Monitor index health regularly</li> </ul> <h3 id="complete-example-e-commerce-optimization" class="position-relative d-flex align-items-center group"> Complete Example: E-Commerce Optimization <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="complete-example-e-commerce-optimization" aria-haspopup="dialog" aria-label="Share link: Complete Example: E-Commerce Optimization"> Share link </button> </h3>Optimize a product catalog query: <h4 id="initial-setup" class="position-relative d-flex align-items-center group"> Initial Setup <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="initial-setup" aria-haspopup="dialog" aria-label="Share link: Initial Setup"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">CREATE GRAPH ECommerce; USE ECommerce; CREATE (:Product {id: 1, name: "Laptop", category: "Electronics", price: 1200, rating: 4.5}), (:Product {id: 2, name: "Mouse", category: "Electronics", price: 25, rating: 4.2}), (:Product {id: 3, name: "Desk", category: "Furniture", price: 350, rating: 4.7}), (:Product {id: 4, name: "Chair", category: "Furniture", price: 200, rating: 4.6}), (:Product {id: 5, name: "Monitor", category: "Electronics", price: 400, rating: 4.4}); </code></pre></div> <h4 id="slow-query" class="position-relative d-flex align-items-center group"> Slow Query <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="slow-query" aria-haspopup="dialog" aria-label="Share link: Slow Query"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">PROFILE MATCH (p:Product) WHERE p.category = 'Electronics' AND p.price < 500 AND p.rating > 4.0 RETURN p.name, p.price, p.rating ORDER BY p.rating DESC; </code></pre></div>Result: SeqScan, 5 rows scanned <h4 id="create-optimal-indexes" class="position-relative d-flex align-items-center group"> Create Optimal Indexes <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="create-optimal-indexes" aria-haspopup="dialog" aria-label="Share link: Create Optimal Indexes"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Composite index for category + price range CREATE INDEX prod_cat_price_idx ON Product(category, price) USING btree; -- Separate index for rating (used in ORDER BY) CREATE INDEX prod_rating_idx ON Product(rating) USING btree; -- Update statistics ANALYZE Product; </code></pre></div> <h4 id="verify-optimization" class="position-relative d-flex align-items-center group"> Verify Optimization <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="verify-optimization" aria-haspopup="dialog" aria-label="Share link: Verify Optimization"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">EXPLAIN MATCH (p:Product) WHERE p.category = 'Electronics' AND p.price < 500 AND p.rating > 4.0 RETURN p.name, p.price, p.rating ORDER BY p.rating DESC; </code></pre></div>Expected plan: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">└─ Project [p.name, p.price, p.rating] └─ Sort [p.rating DESC] using prod_rating_idx └─ Filter [p.rating > 4.0] └─ IndexScan [prod_cat_price_idx] Condition: category = 'Electronics' AND price < 500 Estimated rows: 2 </code></pre></div> <h4 id="performance-comparison" class="position-relative d-flex align-items-center group"> Performance Comparison <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="performance-comparison" aria-haspopup="dialog" aria-label="Share link: Performance Comparison"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">PROFILE MATCH (p:Product) WHERE p.category = 'Electronics' AND p.price < 500 AND p.rating > 4.0 RETURN p.name, p.price, p.rating ORDER BY p.rating DESC; </code></pre></div>Before: 5 rows scanned, 0.25ms After: 2 rows scanned, 0.08ms Improvement: 68% faster, 60% fewer rows <h4 id="what-you-learned-10" class="position-relative d-flex align-items-center group"> What You Learned <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="what-you-learned-10" aria-haspopup="dialog" aria-label="Share link: What You Learned"> Share link </button> </h4><ul> <li>Combine indexes for complex queries</li> <li>Separate indexes for filter vs sort</li> <li>Composite indexes reduce scan size</li> <li>ORDER BY can use indexes too</li> </ul> <h3 id="best-practices" class="position-relative d-flex align-items-center group"> Best Practices <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="best-practices" aria-haspopup="dialog" aria-label="Share link: Best Practices"> Share link </button> </h3> <h4 id="index-creation-strategy" class="position-relative d-flex align-items-center group"> Index Creation Strategy <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="index-creation-strategy" aria-haspopup="dialog" aria-label="Share link: Index Creation Strategy"> Share link </button> </h4><ol> <li>Profile first: Use PROFILE to find slow queries</li> <li>Identify patterns: Look for common WHERE/ORDER BY columns</li> <li>Create selectively: Don’t over-index (balance read vs write performance)</li> <li>Test impact: Compare EXPLAIN before and after</li> <li>Monitor usage: Drop unused indexes</li> </ol> <h4 id="index-selection-guidelines" class="position-relative d-flex align-items-center group"> Index Selection Guidelines <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="index-selection-guidelines" aria-haspopup="dialog" aria-label="Share link: Index Selection Guidelines"> Share link </button> </h4>Always index: <ul> <li>Primary keys and unique constraints</li> <li>Foreign key columns (join conditions)</li> <li>Columns in frequent WHERE clauses</li> <li>ORDER BY and GROUP BY columns</li> </ul> Consider indexing: <ul> <li>Columns with high cardinality (many distinct values)</li> <li>Columns in complex queries</li> <li>Frequently accessed properties</li> </ul> Don’t index: <ul> <li>Low cardinality columns (few distinct values, like boolean)</li> <li>Rarely queried properties</li> <li>Columns updated frequently (write overhead)</li> </ul> <h4 id="performance-rules-of-thumb" class="position-relative d-flex align-items-center group"> Performance Rules of Thumb <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="performance-rules-of-thumb" aria-haspopup="dialog" aria-label="Share link: Performance Rules of Thumb"> Share link </button> </h4><ul> <li>Indexes speed up reads: Significant improvement for large datasets</li> <li>Indexes add write overhead: Consider trade-offs when creating indexes</li> <li>Composite indexes: 2-3 columns maximum recommended</li> <li>Index space: Varies based on data and index type</li> <li>Statistics: Update after significant data changes</li> </ul> <h3 id="practice-exercises" class="position-relative d-flex align-items-center group"> Practice Exercises <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="practice-exercises" aria-haspopup="dialog" aria-label="Share link: Practice Exercises"> Share link </button> </h3> <h4 id="exercise-1-optimize-range-query" class="position-relative d-flex align-items-center group"> Exercise 1: Optimize Range Query <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="exercise-1-optimize-range-query" aria-haspopup="dialog" aria-label="Share link: Exercise 1: Optimize Range Query"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Create test data CREATE GRAPH RangeTest; USE RangeTest; CREATE (:Event {name: "Event_1", timestamp: 1609459200, priority: 1}), (:Event {name: "Event_2", timestamp: 1609545600, priority: 2}), (:Event {name: "Event_3", timestamp: 1609632000, priority: 1}), (:Event {name: "Event_4", timestamp: 1609718400, priority: 3}); -- Slow query MATCH (e:Event) WHERE e.timestamp > 1609500000 AND e.timestamp < 1609700000 RETURN e.name, e.timestamp ORDER BY e.timestamp; </code></pre></div>Task: Create an index to optimize this query. Verify with EXPLAIN. <h4 id="exercise-2-composite-index-design" class="position-relative d-flex align-items-center group"> Exercise 2: Composite Index Design <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="exercise-2-composite-index-design" aria-haspopup="dialog" aria-label="Share link: Exercise 2: Composite Index Design"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Query pattern MATCH (u:User) WHERE u.country = 'USA' AND u.age > 25 AND u.active = true RETURN u.name, u.email; </code></pre></div>Task: Design optimal composite index. Consider column order. <h4 id="exercise-3-index-type-selection" class="position-relative d-flex align-items-center group"> Exercise 3: Index Type Selection <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="exercise-3-index-type-selection" aria-haspopup="dialog" aria-label="Share link: Exercise 3: Index Type Selection"> Share link </button> </h4>For each query, choose the best index type: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Query A WHERE user.email = 'alice@example.com' -- Query B WHERE product.price BETWEEN 50 AND 100 -- Query C WHERE fulltext_match(article.content, "graph database") -- Query D WHERE vector_distance_l2(image.embedding, $query_vec) < 0.3 -- Query E WHERE 'featured' IN product.tags </code></pre></div> <h3 id="solutions" class="position-relative d-flex align-items-center group"> Solutions <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="solutions" aria-haspopup="dialog" aria-label="Share link: Solutions"> Share link </button> </h3><details> <summary>Exercise 1 Solution</summary> <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Create B-tree index for range query CREATE INDEX event_timestamp_idx ON Event(timestamp) USING btree; -- Verify usage EXPLAIN MATCH (e:Event) WHERE e.timestamp > 1609500000 AND e.timestamp < 1609700000 RETURN e.name, e.timestamp ORDER BY e.timestamp; -- Should show IndexScan with event_timestamp_idx </code></pre></div></details> <details> <summary>Exercise 2 Solution</summary> <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Column order: equality first, then range, then equality -- country (equality, medium selectivity) -- age (range, high selectivity) -- active (equality, low selectivity) CREATE INDEX user_country_age_active_idx ON User(country, age, active) USING btree; -- Alternative (if age range is wide): CREATE INDEX user_country_active_age_idx ON User(country, active, age) USING btree; -- Verify EXPLAIN MATCH (u:User) WHERE u.country = 'USA' AND u.age > 25 AND u.active = true RETURN u.name, u.email; </code></pre></div></details> <details> <summary>Exercise 3 Solution</summary> <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Query A: Hash index (exact match) CREATE INDEX user_email_idx ON User(email) USING hash; -- Query B: B-tree index (range query) CREATE INDEX product_price_idx ON Product(price) USING btree; -- Query C: Full-text index CREATE INDEX article_content_idx ON Article(content) USING fulltext; -- Query D: Vector index (similarity search) CREATE INDEX image_embedding_idx ON Image(embedding) USING vector; -- Query E: Bloom filter (membership testing) CREATE INDEX product_tags_idx ON Product(tags) USING bloom; </code></pre></div></details> <h3 id="troubleshooting" class="position-relative d-flex align-items-center group"> Troubleshooting <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="troubleshooting" aria-haspopup="dialog" aria-label="Share link: Troubleshooting"> Share link </button> </h3> <h4 id="index-not-used-by-optimizer" class="position-relative d-flex align-items-center group"> Index Not Used by Optimizer <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="index-not-used-by-optimizer" aria-haspopup="dialog" aria-label="Share link: Index Not Used by Optimizer"> Share link </button> </h4>Problem: EXPLAIN shows SeqScan despite index existing Solutions: <ol> <li>Update statistics: <code>ANALYZE Person;</code></li> <li>Check index covers query columns</li> <li>Verify WHERE clause uses indexed columns</li> <li>Try composite index for multiple predicates</li> <li>Check data distribution (very small tables may use SeqScan)</li> </ol> <h4 id="slow-index-creation" class="position-relative d-flex align-items-center group"> Slow Index Creation <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="slow-index-creation" aria-haspopup="dialog" aria-label="Share link: Slow Index Creation"> Share link </button> </h4>Problem: CREATE INDEX takes too long Solutions: <ol> <li>Build indexes during maintenance windows</li> <li>Use <code>CONCURRENTLY</code> option (if available)</li> <li>Increase memory for index build</li> <li>Consider smaller sample size for partial indexes</li> </ol> <h4 id="query-slower-after-adding-index" class="position-relative d-flex align-items-center group"> Query Slower After Adding Index <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="query-slower-after-adding-index" aria-haspopup="dialog" aria-label="Share link: Query Slower After Adding Index"> Share link </button> </h4>Problem: Performance degraded post-indexing Solutions: <ol> <li>Update statistics: <code>ANALYZE</code></li> <li>Check if wrong index chosen</li> <li>Consider dropping conflicting indexes</li> <li>Verify index type matches query pattern</li> <li>Check write overhead on heavily updated tables</li> </ol> <h3 id="next-steps" class="position-relative d-flex align-items-center group"> Next Steps <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="next-steps" aria-haspopup="dialog" aria-label="Share link: Next Steps"> Share link </button> </h3>Continue your learning journey: <ol> <li><a href="/docs/tutorials/graph-algorithms-tutorial/" >Graph Algorithms Tutorial</a> - PageRank, centrality, community detection</li> <li><a href="/docs/performance/" >Performance Tuning Guide</a> - Advanced optimization techniques</li> <li><a href="/docs/gql/guide/" >Query Language Reference</a> - Complete GQL syntax</li> <li><a href="/docs/query/indexing-and-optimization/" >Indexing Guide</a> - Comprehensive indexing documentation</li> </ol> <h3 id="quick-reference" class="position-relative d-flex align-items-center group"> Quick Reference <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="quick-reference" aria-haspopup="dialog" aria-label="Share link: Quick Reference"> Share link </button> </h3> <h4 id="index-commands" class="position-relative d-flex align-items-center group"> Index Commands <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="index-commands" aria-haspopup="dialog" aria-label="Share link: Index Commands"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Create index CREATE INDEX idx_name ON Label(property) USING btree; -- Composite index CREATE INDEX idx_name ON Label(prop1, prop2) USING btree; -- Drop index DROP INDEX idx_name; -- List indexes SHOW INDEXES ON Label; -- Update statistics ANALYZE Label; </code></pre></div> <h4 id="index-types" class="position-relative d-flex align-items-center group"> Index Types <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="index-types" aria-haspopup="dialog" aria-label="Share link: Index Types"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">USING btree -- Range queries, ordering USING hash -- Exact matches only USING fulltext -- Text search USING vector -- Similarity search USING bloom -- Membership testing </code></pre></div> <h4 id="query-analysis" class="position-relative d-flex align-items-center group"> Query Analysis <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="query-analysis" aria-haspopup="dialog" aria-label="Share link: Query Analysis"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">EXPLAIN ... -- Show query plan (no execution) PROFILE ... -- Execute and show performance ANALYZE Label; -- Update statistics </code></pre></div><hr> Tutorial Complete! You now understand indexing and query optimization in Geode. Next: <a href="/docs/tutorials/graph-algorithms-tutorial/" >Graph Algorithms Tutorial</a>