Tag: Indexes

Documentation tagged with indexes in the Geode graph database. This comprehensive collection covers index design, implementation strategies, performance optimization, maintenance procedures, and best practices for leveraging indexes to accelerate graph queries and ensure optimal database performance. <h3 id="overview" class="position-relative d-flex align-items-center group"> 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="overview" aria-haspopup="dialog" aria-label="Share link: Overview"> Share link </button> </h3><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>Indexes are the cornerstone of database performance, dramatically reducing query execution time by providing fast access paths to data. In Geode, indexes accelerate node lookups, property searches, relationship traversals, and complex pattern matching operations. Understanding index design and management is essential for building high-performance graph applications. Key benefits of indexes: <ul> <li>Fast Lookups: O(log n) access instead of O(n) scans</li> <li>Efficient Filtering: Quick predicate evaluation on indexed properties</li> <li>Relationship Traversals: Accelerated edge navigation</li> <li>Unique Constraints: Enforce data integrity at database level</li> <li>Query Optimization: Enable better execution plans</li> </ul> <h3 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> </h3> <h4 id="property-indexes" class="position-relative d-flex align-items-center group"> Property 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="property-indexes" aria-haspopup="dialog" aria-label="Share link: Property Indexes"> Share link </button> </h4>Index individual node or edge properties: Basic Property Index: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Create index on single property CREATE INDEX user_email_idx ON User(email); // Accelerates queries like: MATCH (u:User) WHERE u.email = 'user@example.com' RETURN u; // Performance: Significantly faster than full scans (workload dependent) </code></pre></div>Benefits: <ul> <li>Equality lookups (property = value)</li> <li>Range queries (property > value, property BETWEEN x AND y)</li> <li>Ordering (ORDER BY indexed_property)</li> <li>Existence checks (WHERE property IS NOT NULL)</li> </ul> <h4 id="unique-indexes" class="position-relative d-flex align-items-center group"> Unique 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="unique-indexes" aria-haspopup="dialog" aria-label="Share link: Unique Indexes"> Share link </button> </h4>Enforce uniqueness while providing fast access: Unique Constraint: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Create unique index CREATE UNIQUE INDEX user_id_idx ON User(user_id); // Enforces constraint: // - No two User nodes can have same user_id // - NULL values allowed (treated as distinct) // - Provides index performance benefits // Accelerates lookups: MATCH (u:User {user_id: '12345'}) RETURN u; </code></pre></div>Use Cases: <ul> <li>Primary keys (user IDs, email addresses)</li> <li>External identifiers (SSN, account numbers)</li> <li>Natural keys (ISBN, SKU)</li> <li>Usernames and handles</li> </ul> <h4 id="composite-indexes" class="position-relative d-flex align-items-center group"> 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="composite-indexes" aria-haspopup="dialog" aria-label="Share link: Composite Indexes"> Share link </button> </h4>Index multiple properties together: Multi-Column Index: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Create composite index CREATE INDEX user_location_idx ON User(city, state, country); // Optimizes queries using indexed prefix: // ✓ Efficient: Uses index fully MATCH (u:User) WHERE u.city = 'Seattle' AND u.state = 'WA' RETURN u; // ✓ Efficient: Uses index on city MATCH (u:User) WHERE u.city = 'Seattle' RETURN u; // ✗ Inefficient: Can't use index (no prefix match) MATCH (u:User) WHERE u.state = 'WA' RETURN u; </code></pre></div>Index Prefix Rule: Composite indexes match queries left-to-right: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Index: (a, b, c) // Can optimize: // - WHERE a = x // - WHERE a = x AND b = y // - WHERE a = x AND b = y AND c = z // Cannot optimize: // - WHERE b = y // - WHERE c = z // - WHERE b = y AND c = z </code></pre></div> <h4 id="covering-indexes" class="position-relative d-flex align-items-center group"> Covering 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="covering-indexes" aria-haspopup="dialog" aria-label="Share link: Covering Indexes"> Share link </button> </h4>Include all query columns in index: Index-Only Scans: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Create covering index CREATE INDEX user_profile_idx ON User(email, name, created_at); // Query satisfied entirely from index: MATCH (u:User) WHERE u.email = 'user@example.com' RETURN u.name, u.created_at; // No node access needed - all data in index // Performance: 2-5x faster than regular index </code></pre></div>Design Strategy: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Identify frequent query pattern SELECT name, email, created_at FROM User WHERE status = 'active' AND region = 'West'; // Create covering index: // - Filter columns first (status, region) // - Return columns last (name, email, created_at) CREATE INDEX user_active_region_idx ON User(status, region, name, email, created_at); </code></pre></div> <h4 id="full-text-indexes" class="position-relative d-flex align-items-center group"> Full-Text 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="full-text-indexes" aria-haspopup="dialog" aria-label="Share link: Full-Text Indexes"> Share link </button> </h4>Search text content efficiently: Text Search Index: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Create full-text index CREATE FULLTEXT INDEX post_content_idx ON Post(title, body); // Enables text search: MATCH (p:Post) WHERE p.title CONTAINS 'graph database' OR p.body CONTAINS 'graph database' RETURN p ORDER BY p.relevance DESC; // Supports: // - Keyword search // - Phrase matching // - Relevance ranking // - Stemming and tokenization </code></pre></div> <h4 id="spatial-indexes" class="position-relative d-flex align-items-center group"> Spatial 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="spatial-indexes" aria-haspopup="dialog" aria-label="Share link: Spatial Indexes"> Share link </button> </h4>Optimize geographic queries: Geo Index: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Create spatial index for location data CREATE SPATIAL INDEX location_idx ON Place(latitude, longitude); // Accelerates proximity queries: MATCH (p:Place) WHERE distance(p.location, point({latitude: 47.6, longitude: -122.3})) < 10000 RETURN p.name, p.location ORDER BY distance(p.location, point({latitude: 47.6, longitude: -122.3})); // Supports: // - Distance calculations // - Bounding box queries // - K-nearest neighbors </code></pre></div> <h3 id="index-design" class="position-relative d-flex align-items-center group"> 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="index-design" aria-haspopup="dialog" aria-label="Share link: Index Design"> Share link </button> </h3> <h4 id="design-principles" class="position-relative d-flex align-items-center group"> Design Principles <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="design-principles" aria-haspopup="dialog" aria-label="Share link: Design Principles"> Share link </button> </h4>Cardinality Considerations: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// High cardinality - excellent for indexes // - email (millions of unique values) // - user_id (all unique) // - order_number (all unique) CREATE UNIQUE INDEX user_email_idx ON User(email); // Medium cardinality - good for indexes // - city (thousands of values) // - zip_code (tens of thousands) // - product_category (hundreds) CREATE INDEX user_city_idx ON User(city); // Low cardinality - poor index candidates // - gender (2-3 values) // - boolean flags (2 values) // - status (3-5 values) // Skip indexing or use in composite index </code></pre></div>Selectivity: Indexes work best when they filter out most rows: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Highly selective - great index // Returns <0.1% of rows MATCH (u:User) WHERE u.email = 'specific@example.com' RETURN u; // Moderately selective - good index // Returns ~5% of rows MATCH (u:User) WHERE u.city = 'Seattle' RETURN u; // Low selectivity - poor index // Returns ~50% of rows MATCH (u:User) WHERE u.account_type = 'free' -- 50% of users RETURN u; </code></pre></div> <h4 id="index-selection-strategy" class="position-relative d-flex align-items-center group"> Index Selection 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-selection-strategy" aria-haspopup="dialog" aria-label="Share link: Index Selection Strategy"> Share link </button> </h4>Step 1: Analyze Query Patterns: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql">-- Identify frequent queries SELECT query_text, execution_count, avg_duration_ms, table_scans FROM system.query_stats WHERE table_scans > 0 ORDER BY execution_count DESC LIMIT 50; </code></pre></div>Step 2: Identify Filter Columns: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Extract WHERE clause columns -- Query: MATCH (u:User) WHERE u.region = 'West' AND u.status = 'active' -- Candidates: region, status -- Query: MATCH (p:Product) WHERE p.category = 'Books' AND p.price > 20 -- Candidates: category, price </code></pre></div>Step 3: Prioritize by Impact: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-python" data-lang="python"># Calculate index impact score impact = execution_count * avg_duration * selectivity # Example: # Query 1: 10,000 executions/day, 500ms avg, 0.01 selectivity # Impact: 10,000 * 500 * 0.01 = 50,000 # Query 2: 1,000 executions/day, 100ms avg, 0.1 selectivity # Impact: 1,000 * 100 * 0.1 = 10,000 # Prioritize Query 1 index </code></pre></div>Step 4: Create Strategic Indexes: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- High-impact indexes first CREATE INDEX user_region_status_idx ON User(region, status); -- Measure improvement EXPLAIN MATCH (u:User) WHERE u.region = 'West' AND u.status = 'active' RETURN u; -- Should show: Index seek instead of full scan </code></pre></div> <h3 id="index-management" class="position-relative d-flex align-items-center group"> Index Management <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-management" aria-haspopup="dialog" aria-label="Share link: Index Management"> Share link </button> </h3> <h4 id="creating-indexes" class="position-relative d-flex align-items-center group"> Creating 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="creating-indexes" aria-haspopup="dialog" aria-label="Share link: Creating Indexes"> Share link </button> </h4>Syntax: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Basic index CREATE INDEX index_name ON NodeType(property); -- Unique index CREATE UNIQUE INDEX index_name ON NodeType(property); -- Composite index CREATE INDEX index_name ON NodeType(prop1, prop2, prop3); -- Conditional index CREATE INDEX index_name ON NodeType(property) WHERE condition; -- Include additional columns (covering index) CREATE INDEX index_name ON NodeType(filter_prop) INCLUDE (return_prop1, return_prop2); </code></pre></div>Online Index Creation: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Create index without blocking writes CREATE INDEX CONCURRENTLY user_email_idx ON User(email); -- Progress tracking SELECT index_name, build_progress, estimated_completion FROM system.index_builds WHERE status = 'in_progress'; </code></pre></div> <h4 id="monitoring-indexes" class="position-relative d-flex align-items-center group"> Monitoring 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="monitoring-indexes" aria-haspopup="dialog" aria-label="Share link: Monitoring Indexes"> Share link </button> </h4>Index Usage Statistics: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- View index usage SELECT index_name, table_name, index_scans, rows_read, last_scan_time, index_size_mb FROM system.index_stats ORDER BY index_scans DESC; </code></pre></div>Unused Indexes: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Find indexes that are never used SELECT index_name, table_name, index_size_mb, created_at FROM system.index_stats WHERE index_scans = 0 AND created_at < NOW() - INTERVAL '30 days' ORDER BY index_size_mb DESC; </code></pre></div>Index Efficiency: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Identify inefficient indexes SELECT index_name, table_name, index_scans, rows_read / NULLIF(index_scans, 0) AS avg_rows_per_scan, index_size_mb FROM system.index_stats WHERE index_scans > 0 AND rows_read / NULLIF(index_scans, 0) > 1000 -- Low selectivity ORDER BY avg_rows_per_scan DESC; </code></pre></div> <h4 id="index-maintenance" class="position-relative d-flex align-items-center group"> 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="index-maintenance" aria-haspopup="dialog" aria-label="Share link: Index Maintenance"> Share link </button> </h4>Rebuild Fragmented Indexes: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Check index health SELECT index_name, fragmentation_percent, page_count, last_rebuild_time FROM system.index_health WHERE fragmentation_percent > 30; -- Rebuild index REINDEX INDEX user_email_idx; -- Rebuild all indexes on table REINDEX TABLE User; </code></pre></div>Update Statistics: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Refresh optimizer statistics ANALYZE User; -- Full database analysis ANALYZE; -- Check statistics freshness SELECT table_name, last_analyze_time, row_count, mod_count FROM system.table_stats WHERE last_analyze_time < NOW() - INTERVAL '7 days'; </code></pre></div> <h4 id="dropping-indexes" class="position-relative d-flex align-items-center group"> Dropping 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="dropping-indexes" aria-haspopup="dialog" aria-label="Share link: Dropping Indexes"> Share link </button> </h4>Remove Unused Indexes: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Drop single index DROP INDEX user_old_idx; -- Drop if exists (safe) DROP INDEX IF EXISTS user_old_idx; -- Drop multiple indexes DROP INDEX user_idx1, user_idx2, user_idx3; </code></pre></div>Impact Analysis Before Dropping: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Check what queries use an index SELECT DISTINCT query_text FROM system.query_plans WHERE index_name = 'user_region_idx'; </code></pre></div> <h3 id="performance-optimization" class="position-relative d-flex align-items-center group"> Performance 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="performance-optimization" aria-haspopup="dialog" aria-label="Share link: Performance Optimization"> Share link </button> </h3> <h4 id="index-selection-by-query-optimizer" class="position-relative d-flex align-items-center group"> Index Selection by Query 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-selection-by-query-optimizer" aria-haspopup="dialog" aria-label="Share link: Index Selection by Query Optimizer"> Share link </button> </h4>How Optimizer Chooses Indexes: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Query with multiple index candidates MATCH (u:User) WHERE u.region = 'West' AND u.email = 'user@example.com' RETURN u; -- Available indexes: -- 1. user_region_idx on (region) -- 2. user_email_idx on (email) -- Optimizer chooses user_email_idx because: -- - Higher selectivity (unique email vs common region) -- - Faster lookup (1 row vs many rows) </code></pre></div>Force Index Usage: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Force specific index with hint MATCH (u:User) USING INDEX user_region_idx WHERE u.region = 'West' RETURN u; </code></pre></div> <h4 id="index-vs-table-scan-decision" class="position-relative d-flex align-items-center group"> Index vs. Table Scan Decision <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-vs-table-scan-decision" aria-haspopup="dialog" aria-label="Share link: Index vs. Table Scan Decision"> Share link </button> </h4>Cost-Based Optimization: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- When index is used: MATCH (u:User) WHERE u.email = 'user@example.com' RETURN u; -- Cost: Index seek (3 I/Os) + 1 row fetch = 4 I/Os -- When table scan is used: MATCH (u:User) WHERE u.account_type = 'premium' RETURN u; -- 50% of rows match -- Cost: Index seek (3 I/Os) + 500,000 row fetches = 500,003 I/Os -- vs Table scan: 10,000 page reads = 10,000 I/Os -- Optimizer chooses table scan (cheaper) </code></pre></div> <h4 id="composite-index-optimization" class="position-relative d-flex align-items-center group"> Composite Index 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="composite-index-optimization" aria-haspopup="dialog" aria-label="Share link: Composite Index Optimization"> Share link </button> </h4>Column Order Matters: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Query pattern: Filter by status, then region, then created_at MATCH (u:User) WHERE u.status = 'active' AND u.region = 'West' AND u.created_at > '2025-01-01' RETURN u; -- Optimal index order: // 1. Most selective first CREATE INDEX user_composite_idx ON User(status, region, created_at); // Or prioritize equality over range CREATE INDEX user_composite_idx ON User(region, status, created_at); // Equality (region, status) then range (created_at) </code></pre></div> <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-design-guidelines" class="position-relative d-flex align-items-center group"> Index Design 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-design-guidelines" aria-haspopup="dialog" aria-label="Share link: Index Design Guidelines"> Share link </button> </h4>DO: <ul> <li>Index primary keys and foreign keys</li> <li>Index columns frequently in WHERE clauses</li> <li>Index columns used in JOIN conditions</li> <li>Index columns in ORDER BY clauses</li> <li>Use composite indexes for multi-column filters</li> <li>Include covering columns for hot queries</li> <li>Monitor index usage and remove unused indexes</li> </ul> DON’T: <ul> <li>Over-index (each index slows writes)</li> <li>Index low-cardinality columns alone</li> <li>Create redundant indexes</li> <li>Index columns rarely queried</li> <li>Forget to update statistics</li> <li>Ignore index fragmentation</li> </ul> <h4 id="write-performance-considerations" class="position-relative d-flex align-items-center group"> Write Performance Considerations <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="write-performance-considerations" aria-haspopup="dialog" aria-label="Share link: Write Performance Considerations"> Share link </button> </h4>Index Overhead: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Each write operation updates all indexes -- Node with 5 indexes: CREATE (u:User { user_id: '12345', email: 'user@example.com', name: 'Alice', city: 'Seattle', created_at: timestamp() }); // Updates: // 1. Primary table storage // 2. user_id_idx // 3. email_idx // 4. name_idx // 5. city_idx // 6. created_at_idx // Total: 6 write operations </code></pre></div>Batch Operations: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Disable indexes during bulk load ALTER INDEX user_email_idx DISABLE; -- Load data LOAD CSV FROM 'users.csv' AS row CREATE (u:User {user_id: row.id, email: row.email, ...}); -- Rebuild indexes ALTER INDEX user_email_idx REBUILD; </code></pre></div> <h4 id="index-monitoring-workflow" class="position-relative d-flex align-items-center group"> Index Monitoring Workflow <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-monitoring-workflow" aria-haspopup="dialog" aria-label="Share link: Index Monitoring Workflow"> Share link </button> </h4>Weekly Review: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- 1. Check unused indexes SELECT index_name, index_size_mb FROM system.index_stats WHERE index_scans = 0 AND created_at < NOW() - INTERVAL '30 days'; -- 2. Identify missing indexes SELECT query_text, execution_count, avg_duration_ms FROM system.query_stats WHERE table_scans > 0 AND execution_count > 100 ORDER BY execution_count * avg_duration_ms DESC LIMIT 20; -- 3. Review index health SELECT index_name, fragmentation_percent FROM system.index_health WHERE fragmentation_percent > 30; -- 4. Update statistics ANALYZE; </code></pre></div> <h3 id="advanced-techniques" class="position-relative d-flex align-items-center group"> Advanced Techniques <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="advanced-techniques" aria-haspopup="dialog" aria-label="Share link: Advanced Techniques"> Share link </button> </h3> <h4 id="partial-indexes" class="position-relative d-flex align-items-center group"> Partial 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="partial-indexes" aria-haspopup="dialog" aria-label="Share link: Partial Indexes"> Share link </button> </h4>Index subset of rows: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Index only active users CREATE INDEX active_users_idx ON User(email) WHERE status = 'active'; -- Much smaller than full index -- Faster for queries on active users only MATCH (u:User) WHERE u.status = 'active' AND u.email = 'user@example.com' RETURN u; </code></pre></div> <h4 id="expression-indexes" class="position-relative d-flex align-items-center group"> Expression 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="expression-indexes" aria-haspopup="dialog" aria-label="Share link: Expression Indexes"> Share link </button> </h4>Index computed values: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Index lowercase email for case-insensitive search CREATE INDEX user_email_lower_idx ON User(LOWER(email)); -- Enables fast case-insensitive lookup MATCH (u:User) WHERE LOWER(u.email) = LOWER('User@Example.com') RETURN u; </code></pre></div> <h4 id="index-organized-tables" class="position-relative d-flex align-items-center group"> Index-Organized Tables <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-organized-tables" aria-haspopup="dialog" aria-label="Share link: Index-Organized Tables"> Share link </button> </h4>Store table data in index order: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Create table ordered by timestamp CREATE NODE TYPE Event ( event_id UUID PRIMARY KEY, timestamp TIMESTAMP, event_type STRING, data JSON ) CLUSTERED BY (timestamp); -- Range queries on timestamp are extremely fast MATCH (e:Event) WHERE e.timestamp > '2025-01-01' AND e.timestamp < '2025-01-31' RETURN e ORDER BY e.timestamp; -- No sort needed, data already in order </code></pre></div> <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-being-used" class="position-relative d-flex align-items-center group"> Index Not Being 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="index-not-being-used" aria-haspopup="dialog" aria-label="Share link: Index Not Being Used"> Share link </button> </h4>Diagnosis: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Check execution plan EXPLAIN MATCH (u:User) WHERE u.email = 'user@example.com' RETURN u; -- If showing table scan instead of index seek: </code></pre></div>Common Causes: <ol> <li>Function on indexed column:</li> </ol> <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- ✗ Index not used MATCH (u:User) WHERE LOWER(u.email) = 'user@example.com' RETURN u; -- ✓ Index used MATCH (u:User) WHERE u.email = 'user@example.com' RETURN u; </code></pre></div><ol start="2"> <li>Type mismatch:</li> </ol> <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- ✗ Index not used (string vs integer) MATCH (u:User) WHERE u.user_id = 12345 -- user_id is string RETURN u; -- ✓ Index used MATCH (u:User) WHERE u.user_id = '12345' RETURN u; </code></pre></div><ol start="3"> <li>Stale statistics:</li> </ol> <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Update optimizer statistics ANALYZE User; </code></pre></div> <h4 id="slow-index-scans" class="position-relative d-flex align-items-center group"> Slow Index Scans <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-scans" aria-haspopup="dialog" aria-label="Share link: Slow Index Scans"> Share link </button> </h4>Issue: Index exists but queries still slow. Solutions: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- 1. Check index selectivity SELECT index_name, rows_read / NULLIF(index_scans, 0) AS avg_rows_per_scan FROM system.index_stats WHERE index_name = 'problematic_idx'; -- High avg_rows_per_scan indicates low selectivity -- 2. Consider composite index for better selectivity CREATE INDEX better_idx ON User(region, status, created_at); -- 3. Use covering index to avoid row fetches CREATE INDEX covering_idx ON User(region) INCLUDE (name, email, created_at); </code></pre></div> <h3 id="integration-with-geode" class="position-relative d-flex align-items-center group"> Integration with Geode <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="integration-with-geode" aria-haspopup="dialog" aria-label="Share link: Integration with Geode"> Share link </button> </h3>Indexes integrate seamlessly with Geode features: <ul> <li>Query Optimizer: Automatic index selection for optimal plans</li> <li>EXPLAIN Command: Shows index usage in execution plans</li> <li>Transactions: Indexes updated atomically within transactions</li> <li>Concurrency: Online index creation without blocking writes</li> <li>Monitoring: Built-in index usage and health metrics</li> <li>Security: RLS policies work efficiently with indexes</li> </ul> <h3 id="related-topics" class="position-relative d-flex align-items-center group"> Related Topics <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="related-topics" aria-haspopup="dialog" aria-label="Share link: Related Topics"> Share link </button> </h3><ul> <li>Query Optimization: Using indexes for better performance</li> <li>Query Performance: Index impact on throughput and latency</li> <li>EXPLAIN Command: Analyzing index usage in query plans</li> <li>Schema Design: Designing schemas with indexing in mind</li> <li>Best Practices: Index design and management best practices</li> <li>Monitoring: Tracking index usage and performance</li> </ul> <hr> Browse the tagged content below to discover comprehensive guides, tutorials, and best practices for indexes in Geode. Learn how to design, create, and manage indexes that dramatically improve query performance and scale your graph database applications to production workloads.

Popular

Related Articles

Constraints and Indexes Guide

Query Performance Tuning Guide

Index Types Reference