Create an index schema for the current database

[SGA-A]

Is your feature request related to a problem? Please describe. We've found that creating an index schema on a table within your database is beneficial to improving the performance of the bot. We haven't paid too much attention on the backend side of the bot, but addressing this can improve the speed of data retrieval. We do not need this feature yet, so this is simply a suggestion. But it may become more important as the size of the database expands in the future.

Describe the solution you'd like Here's an example python script for adding an index onto a specific field named 'bank' within a table named 'bank':

# Create an index on the 'bank' field
await cursor.execute("CREATE INDEX IF NOT EXISTS idx_bank ON bank (bank)")

We'll most likely be using the database browser for SQLite to implement this instead, it's easier to implement this way.

Describe alternatives you've considered The alternative is to simply ignore this, as it's a suggestion to improve data retrieval. The retrieval process is already rapid as it is right now, but adding this could possibly make the process instant. Since we do a lot of updating data as well though, this process is not necessarily a good idea. You can see the additional context section below, as to why this is.

Additional context I asked ChatGPT why an index schema would be beneficial. The below is it's response:

Yes, creating an index schema on a table within your database can be beneficial, and the decision to do so depends on various factors related to the specific use case and queries performed on the table. Indexing is a database optimization technique that improves the speed of data retrieval operations. Here are some reasons why you might consider creating an index:

Faster Query Performance:

Indexing allows the database engine to quickly locate and retrieve specific rows based on the indexed columns. This results in faster query performance, especially for SELECT statements and WHERE clauses.

Sorting and Filtering:

If your queries frequently involve sorting or filtering based on certain columns, creating an index on those columns can significantly improve the efficiency of these operations.

Join Operations:

Indexing is particularly useful in speeding up join operations, where data from multiple tables is combined. Indexes on the columns used in join conditions can optimize the process.

Unique Constraints:

Indexes can enforce the uniqueness of values in specific columns, preventing the insertion of duplicate values. This is especially important for maintaining data integrity.

Primary and Foreign Keys:

Primary key columns and columns used in foreign key relationships are typically automatically indexed. These indexes help maintain the integrity of relationships and enhance query performance.

Aggregation Functions:

If your queries involve aggregation functions (e.g., SUM, AVG, COUNT) on specific columns, creating indexes on those columns can improve the performance of such operations.

Range Queries:

Indexes are effective in speeding up range queries where you want to retrieve a range of values from a column. This is useful for scenarios like date ranges or numeric intervals.

Reduced Disk I/O:

Indexing can reduce the amount of disk I/O required for query processing, as the database engine can quickly locate the relevant data without scanning the entire table.

It's important to note that while indexes can significantly improve read performance, they may have some impact on write performance, as indexes need to be maintained whenever data is inserted, updated, or deleted. Therefore, the decision to create indexes should be based on a careful analysis of the specific workload and query patterns in your application.

[SGA-A]

Note that if we want to do this, we will have to rewrite the methods used within the db for every single table that has an index schema. The priority is quite low here and given the extensive time required to rewrite, the functionality will probably not be worked on any time soon.

[SGA-A]

Not needed as SQLite automatically creates indexes for tables which contain a PK:

INTEGER PRIMARY KEY columns aside, both UNIQUE and PRIMARY KEY constraints are implemented by creating an index in the database (in the same way as a "CREATE UNIQUE INDEX" statement would). Such an index is used like any other index in the database to optimize queries. As a result, there often no advantage (but significant overhead) in creating an index on a set of columns that are already collectively subject to a UNIQUE or PRIMARY KEY constraint.

Sourced from this StackOverflow post.