3

I have a SQLite DB with only one table of size ~1.5 MB (in fact, there are ~30 tables in total, but each table is stored in a separate .db file).

When I use EXPLAIN QUERY PLAN for any table, it shows a full table scan, which as far as I know is not good. However, non of the tables have index and also, the speed of select queries are fast.

So, I was wondering whether I should add some indexes on our tables or just keep them as they are? (Now, tables have max 5k rows and in future they may go maximum up to 100k rows).

PS. number of selects and inserts are almost the same..

All ideas are highly appreciated.

Improve this question
1
  • introduction of indexes depends upon the table structure and reporting/select logic, what are the queries which you feel degraded do share the query and table structure to get better insight
    – Nawaz Sohail
    Commented Nov 1, 2016 at 14:13

2 Answers 2

Reset to default
5

Now, tables have max 5k rows and in future they may go maximum up to 100k rows

In cases like this, where time and resource permits, the best way to check this is to manufacture realistic looking data of that size and test your application against that to see how well it scales (or if it doesn't, where the bottlenecks lie). Make sure your data is realistic though: ~100,000 identical rows won't be a good test as the indexes will not be selective enough to be useful, likewise truly random numbers in the column where the data is expected to "bunch" would also not be the most useful test.

Improve this answer
4
  • Thanks for your answer. Sorry, but I did not understand what you mean by "the best way to check this is to manufacture realistic looking data of that size ...". could you explain it a bit more?
    – monamona
    Commented Nov 2, 2016 at 8:16
  • 2
    @monamona: By that I mean that if you expect the data to scale to 100K rows over time you should create a development/testing copy of the application with 100K rows in those tables. The data should look like real data (not be thousands of identical rows, have a similar balance of short/long/high/low/null values, and so forth) as much as possible. That way you don't need to guess how the database and application will behave at the expected scale - you can run it with that size of data and test its performance directly before it ever becomes an issue in production.
    – David Spillett
    Commented Nov 2, 2016 at 10:53
  • To create such table, i.e., generating 100k rows, could I just fill it with the copy of the same data (the 5k that already exists)?
    – monamona
    Commented Nov 2, 2016 at 12:37
  • 3
    Creating good test data can be an art form in its own right and can very much depend on what the data actually is and how it is referenced, unfortunately. Completely duplicating data is often acceptable and usually good enough but sometimes creates unrealistic results for some index optimisation tests (as it may be too artificial to have many of the same value and the same number of every value so query planners may take a different route when looking for single values). There are tools like fakenamegenerator.com that can help for certain data types but I usually roll my own.
    – David Spillett
    Commented Nov 2, 2016 at 15:17
2

The question to ask is what pain are you trying to fix? Are queries running slow? Is there a CPU problem when running multiple queries? Is memory being affected? Is there one user that says things are running slow?

If there is currently no issues with the way it is currently configured and you don't see a large increase in data size or the number of users, then why change it? However, having some type of index is usually a good idea. A clustered index on the primary key in a table is usually a good idea, but not the only correct solution as there are non-clustered index solutions.

The full table scan is usually due to a lack of indexes, so it has to search the entire table to find the results requested. This isn't always bad, just depends on the number of users, number of transactions and hardware settings.

Hope that helps.

Improve this answer
3
  • Thanks for your answer. we don't have any issued with the current DBs, but there are some of our tables that will get larger during the time and that's why I asked if I have to consider applying indexes on those tables..
    – monamona
    Commented Nov 2, 2016 at 9:20
  • We will have some select on these tables and also insert..
    – monamona
    Commented Nov 2, 2016 at 9:20
  • Performance, unfortunately, isn't an exact science. Part of we consider is what is good enough versus what is needed. Keep in mind that adding indexes can indeed increase performance, however each index creates a duplicate of your data that any DUI ( Delete, Update, Insert ) will have to modify as well and that will take more time all the way around, including maintenance.
    – bwilliamson
    Commented Nov 2, 2016 at 13:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.