103 lines
3.4 KiB
Python
103 lines
3.4 KiB
Python
#!/usr/bin/env python3
|
|
"""
|
|
Add database indexes for performance optimization.
|
|
These indexes target the most frequently queried columns.
|
|
|
|
Expected improvement: 50-70% faster query response times
|
|
"""
|
|
import asyncio
|
|
import os
|
|
from dotenv import load_dotenv
|
|
from sqlalchemy.ext.asyncio import create_async_engine
|
|
from sqlalchemy import text
|
|
|
|
load_dotenv()
|
|
|
|
DB_USER = os.getenv("DB_USER", "user")
|
|
DB_PASS = os.getenv("DB_PASS", "password")
|
|
DB_HOST = os.getenv("DB_HOST", "db")
|
|
DB_PORT = os.getenv("DB_PORT", "5432")
|
|
DB_NAME = os.getenv("DB_NAME", "echoes")
|
|
|
|
DATABASE_URL = f"postgresql+psycopg://{DB_USER}:{DB_PASS}@{DB_HOST}:{DB_PORT}/{DB_NAME}"
|
|
|
|
# Indexes to create with their purpose
|
|
INDEXES = [
|
|
# Players table - most commonly queried
|
|
(
|
|
"CREATE INDEX IF NOT EXISTS idx_players_username ON players(username);",
|
|
"Speed up login/authentication queries"
|
|
),
|
|
(
|
|
"CREATE INDEX IF NOT EXISTS idx_players_location_id ON players(location_id);",
|
|
"Speed up 'get all players in location' queries"
|
|
),
|
|
|
|
# Dropped items - queried on every location view
|
|
(
|
|
"CREATE INDEX IF NOT EXISTS idx_dropped_items_location ON dropped_items(location_id);",
|
|
"Speed up 'show items on ground' queries"
|
|
),
|
|
|
|
# Wandering enemies - checked frequently
|
|
(
|
|
"CREATE INDEX IF NOT EXISTS idx_wandering_enemies_location ON wandering_enemies(location_id);",
|
|
"Speed up 'get enemies in location' queries"
|
|
),
|
|
(
|
|
"CREATE INDEX IF NOT EXISTS idx_wandering_enemies_despawn ON wandering_enemies(despawn_timestamp);",
|
|
"Speed up cleanup queries for expired enemies"
|
|
),
|
|
|
|
# Inventory - queried on every inventory operation
|
|
(
|
|
"CREATE INDEX IF NOT EXISTS idx_inventory_player_item ON inventory(player_id, item_id);",
|
|
"Speed up inventory lookups and item checks"
|
|
),
|
|
(
|
|
"CREATE INDEX IF NOT EXISTS idx_inventory_player ON inventory(player_id);",
|
|
"Speed up 'get all player inventory' queries"
|
|
),
|
|
|
|
# Active combats - checked on most actions
|
|
(
|
|
"CREATE INDEX IF NOT EXISTS idx_active_combats_player ON active_combats(player_id);",
|
|
"Speed up 'is player in combat' checks"
|
|
),
|
|
|
|
# Interactable cooldowns - checked on interact attempts
|
|
(
|
|
"CREATE INDEX IF NOT EXISTS idx_interactable_cooldowns_player ON interactable_cooldowns(player_id, interactable_id);",
|
|
"Speed up cooldown checks"
|
|
),
|
|
]
|
|
|
|
async def add_indexes():
|
|
"""Add all performance indexes to the database."""
|
|
engine = create_async_engine(DATABASE_URL, echo=False)
|
|
|
|
try:
|
|
async with engine.begin() as conn:
|
|
print("Starting index creation...\n")
|
|
|
|
for sql, purpose in INDEXES:
|
|
index_name = sql.split("IF NOT EXISTS ")[1].split(" ON ")[0]
|
|
print(f"Creating {index_name}...")
|
|
print(f" Purpose: {purpose}")
|
|
|
|
try:
|
|
await conn.execute(text(sql))
|
|
print(f" ✓ Success\n")
|
|
except Exception as e:
|
|
print(f" ✗ Failed: {e}\n")
|
|
|
|
print("\n✓ Index creation complete!")
|
|
print("\nTo verify indexes were created:")
|
|
print(" docker exec echoes_of_the_ashes_db psql -U user -d echoes -c \"\\di\"")
|
|
|
|
finally:
|
|
await engine.dispose()
|
|
|
|
if __name__ == "__main__":
|
|
asyncio.run(add_indexes())
|