Indexing, Performance & Database Security

As your database grows, queries can become slow. Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Think of an index like a book's table of contents. Database security involves user permissions, backups, and protecting against SQL injection — one of the most common web vulnerabilities.

• Create an index: CREATE INDEX idx_city ON students(city); — speeds up queries filtering by city.
• Never concatenate user input directly into SQL — use parameterized queries (prepared statements).
• Backup your database regularly: mysqldump -u root rescue_academy > backup.sql
• Grant least-privilege permissions: GRANT SELECT ON rescue_academy.* TO 'readonly_user';

💡 Practical Skill: Schedule automated backups with cron (Linux) or Task Scheduler (Windows). Use MySQL Workbench or pgAdmin to manage users and permissions visually. Always test backups by restoring to a staging server.

-- Create an index to speed up city-based searchesCREATE INDEX idx_students_city ON students(city); -- Show index usage (MySQL)EXPLAIN SELECT * FROM students WHERE city = 'Juba';-- PostgreSQL: EXPLAIN ANALYZE SELECT ... -- Create a read-only user for reporting-- MySQL:CREATE USER 'reporter'@'localhost' IDENTIFIED BY 'secure_password';GRANT SELECT ON rescue_academy.* TO 'reporter'@'localhost'; -- PostgreSQL:-- CREATE USER reporter WITH PASSWORD 'secure_password';-- GRANT CONNECT ON DATABASE rescue_academy TO reporter;-- GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporter; -- Parameterized query example (pseudo-code in Python/Java)-- Python: cursor.execute("SELECT * FROM students WHERE city = %s", (user_input,))-- Java:  PreparedStatement stmt = conn.prepareStatement("SELECT * FROM students WHERE city = ?"); -- Backup command (run in terminal, not inside SQL prompt)-- mysqldump -u root -p rescue_academy > rescue_academy_backup.sql-- pg_dump -U postgres rescue_academy > rescue_academy_backup.sql