How can you properly optimize sqflite query performance for a genuinely large dataset using database indexes, and what genuine tradeoffs come together with actually adding an index to a given specific table?

A database index dramatically speeds up query performance for a specific given column that is genuinely frequently used within a WHERE clause or a JOIN condition, by letting SQLite efficiently locate genuinely matching rows without needing to actually scan through every single row within an entire table one by one, though adding an index does come with the genuine tradeoff of slightly slowing down insert and update operations, since that particular index itself also genuinely needs to be properly kept up to date, meaning indexes should genuinely be added specifically and deliberately for columns that are actually genuinely frequently queried, rather than simply indexing absolutely every single column indiscriminately.

await db.execute('CREATE INDEX idx_customer_id ON orders(customer_id)');