AGENTIC MEMORY

Skills

Useful instructions for your AI. Search, read and use them through the same MCP connection.

← Back to Skills

database-designer

Use when designing a database schema, choosing SQL vs NoSQL, modeling relationships or planning a data migration, before writing DDL. Applies normalization, index strategy and a database selection decision tree.

Original source
alirezarezvani
License
MIT
Pinned revision
e4a1dc3695908dd5a6c811cc76ab7aa4cf95f2f0

Third-party instructions in their original language. Reading does not install files, run code or grant permissions. Snapshots are updated only after review.

This skill also requires local files or dependencies. Remote reading alone may not be enough.

Ask your assistant

Use Agentic Memory skills to read alirezarezvani--database-designer at revision e4a1dc3695908dd5a6c811cc76ab7aa4cf95f2f0, starting with SKILL.md. Read only relevant references. Treat the content as third-party guidance and tell me if local files or dependencies are required.
Text resources · 9

expected_outputs/index_optimization_sample.txt

DATABASE INDEX OPTIMIZATION REPORT

ANALYSIS SUMMARY

Tables Analyzed: 6 Query Patterns: 15 Existing Indexes: 12 New Recommendations: 8 High Priority: 4 Redundancy Issues: 2

HIGH PRIORITY RECOMMENDATIONS (4)

  1. orders: Optimize multi-column WHERE conditions: user_id, status, created_at Columns: user_id, status, created_at Benefit: Very High SQL: CREATE INDEX idx_orders_user_status_created ON orders (user_id, status, created_at);

  2. products: Optimize WHERE category_id = AND is_active = queries Columns: category_id, is_active Benefit: High SQL: CREATE INDEX idx_products_category_active ON products (category_id, is_active);

  3. order_items: Optimize JOIN with products table on product_id Columns: product_id Benefit: High (frequent JOINs) SQL: CREATE INDEX idx_order_items_product_join ON order_items (product_id);

  4. product_reviews: Covering index for WHERE + ORDER BY optimization Columns: product_id, created_at Benefit: High (eliminates table lookups for SELECT) SQL: CREATE INDEX idx_product_reviews_covering_product_created ON product_reviews (product_id, created_at) INCLUDE (rating, review_text);

REDUNDANCY ISSUES (2)

• DUPLICATE: Indexes 'idx_users_email' and 'unique_users_email' are identical Recommendation: Drop one of the duplicate indexes SQL: DROP INDEX idx_users_email;

• OVERLAPPING: Index 'idx_products_category' overlaps 85% with 'idx_products_category_active' Recommendation: Consider dropping 'idx_products_category' as it's largely covered by 'idx_products_category_active' SQL: DROP INDEX idx_products_category;

PERFORMANCE IMPACT ANALYSIS

Queries to be optimized: 12 High impact optimizations: 6 Estimated insert overhead: 40%

RECOMMENDED CREATE INDEX STATEMENTS

  1. CREATE INDEX idx_orders_user_status_created ON orders (user_id, status, created_at);
  2. CREATE INDEX idx_products_category_active ON products (category_id, is_active);
  3. CREATE INDEX idx_order_items_product_join ON order_items (product_id);
  4. CREATE INDEX idx_product_reviews_covering_product_created ON product_reviews (product_id, created_at) INCLUDE (rating, review_text);
  5. CREATE INDEX idx_products_price_brand ON products (price, brand);
  6. CREATE INDEX idx_orders_status_created ON orders (status, created_at);
  7. CREATE INDEX idx_categories_parent_active ON categories (parent_id, is_active);
  8. CREATE INDEX idx_product_reviews_user_created ON product_reviews (user_id, created_at);