The SQLite Index Suggester
sqlite.org
The SQLite Index Suggester
1–10 of 26 posts
Re: The SQLite Index Suggester
#2This sounds really cool!
I've sometimes wondered why server-based RDBMSs don't offer something like this. Is it too hard to implement? Or did people just not think of it? Or do they have something like this and I just never learned about it?
Re: The SQLite Index Suggester
#3This sounds really cool! I've sometimes wondered why server-based RDBMSs don't offer something like this. Is it too hard to implement? Or did people just not think of it? Or do they have something like this and I just never learned about it?
Microsoft SQL Server definitely has suggestions for missing indexes. The quality of the suggestions are debatable though
Re: The SQLite Index Suggester
#4I did something similar for a NoSQL database [1].
The biggest surprise was how much the query performance can change for an index when the data distribution changes slightly. For example using a real distribution for an 'age' field instead of just using a random number like in the test data.
Re: The SQLite Index Suggester
#5This sounds really cool! I've sometimes wondered why server-based RDBMSs don't offer something like this. Is it too hard to implement? Or did people just not think of it? Or do they have something like this and I just never learned about it?
Microsoft SQL Server definitely has suggestions for missing indexes. The quality of the suggestions are debatable though
Thank you!
Re: The SQLite Index Suggester
#6This sounds really cool! I've sometimes wondered why server-based RDBMSs don't offer something like this. Is it too hard to implement? Or did people just not think of it? Or do they have something like this and I just never learned about it?
Microsoft SQL Server definitely has suggestions for missing indexes. The quality of the suggestions are debatable though
sql server has the index tuning wizard (itwiz) that will sample your data and make suggestions, and more lately the missing index stuff as a consequence of how the execution planner is designed (iirc it tries to generate optimal plans regardless of what indexes are available and when plans are eliminated because the necessary indexes don't exist it emits records for the various "missing index" dmvs)
Re: The SQLite Index Suggester
#7This sounds really cool! I've sometimes wondered why server-based RDBMSs don't offer something like this. Is it too hard to implement? Or did people just not think of it? Or do they have something like this and I just never learned about it?
Microsoft SQL Server definitely has suggestions for missing indexes. The quality of the suggestions are debatable though
Indeed. It's even more fun when it starts generating really bad execution plans for table statistics it has completely got wrong, almost always while I'm eating my lunch.
Re: The SQLite Index Suggester
#8This sounds really cool! I've sometimes wondered why server-based RDBMSs don't offer something like this. Is it too hard to implement? Or did people just not think of it? Or do they have something like this and I just never learned about it?
You'd probably enjoy this other discussion on the front page: https://news.ycombinator.com/item?id=31990836
Re: The SQLite Index Suggester
#9I find it interesting, as recently I was reading about how complex it would/could be to create an index suggester.
https://www.depesz.com/2021/10/22/why-is-it-hard-to-automati...
Re: The SQLite Index Suggester
#10I did something similar for a NoSQL database [1]. The biggest surprise was how much the query performance can change for an index when the data distribution changes slightly. For example using a real distribution for an 'age' field instead of just using a random number like in the test data. [1] https://rxdb.info/query-optimizer.html
The 'age' thing might be due to the difference between floats and ints?
Integer "age" has many repeats, but random floats are unique. That, or random ints might be from a large pool, again not many repeats.