File size: 1,877 Bytes
c293f7c | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 | #!/usr/bin/env python3
"""
Database optimization script - Add indexes for better performance
"""
import sqlite3
import os
from pathlib import Path
def optimize_database():
"""Add indexes to improve query performance"""
# Get database path
data_dir = os.path.join(os.path.dirname(os.path.dirname(__file__)), 'data')
db_path = os.path.join(data_dir, 'enhanced_fake_news.db')
if not os.path.exists(db_path):
print("Database not found!")
return
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
try:
print("🔧 Optimizing database performance...")
# Add indexes for common queries
indexes = [
"CREATE INDEX IF NOT EXISTS idx_events_state ON events(state)",
"CREATE INDEX IF NOT EXISTS idx_events_verdict ON events(fake_news_verdict)",
"CREATE INDEX IF NOT EXISTS idx_events_timestamp ON events(timestamp)",
"CREATE INDEX IF NOT EXISTS idx_events_state_verdict ON events(state, fake_news_verdict)",
"CREATE INDEX IF NOT EXISTS idx_events_timestamp_desc ON events(timestamp DESC)"
]
for index_sql in indexes:
cursor.execute(index_sql)
print(f"✅ Created index: {index_sql.split('idx_')[1].split(' ON')[0]}")
# Analyze tables for better query planning
cursor.execute("ANALYZE")
print("✅ Analyzed tables for query optimization")
# Vacuum to optimize storage
cursor.execute("VACUUM")
print("✅ Vacuumed database for storage optimization")
conn.commit()
print("🚀 Database optimization complete!")
except Exception as e:
print(f"❌ Error optimizing database: {e}")
finally:
conn.close()
if __name__ == "__main__":
optimize_database() |