News for hackers. Stuff that matters.
Re:The SQLite Index Suggester by krylon on Tuesday July 05, 2022 @02:33PM (#31991966)
This 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 by A321321 on Tuesday July 05, 2022 @02:35PM (#31991986)
Microsoft SQL Server definitely has suggestions for missing indexes. The quality of the suggestions are debatable though
Re:The SQLite Index Suggester by gfody on Tuesday July 05, 2022 @02:44PM (#31992102)
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 by dmitriid on Tuesday July 05, 2022 @04:44PM (#31993460)
Microsoft SQL Server query analyzer was was essential in identifying missing indexes. Wherever you saw "full table scan" on a table, you knew it was missing an index.

I don't know if it's still around, but in mid-2000s it was light years ahead of any other database.

Re:The SQLite Index Suggester by striking on Tuesday July 05, 2022 @06:55PM (#31994748)
https://explain.dalibo.com/ and https://explain.depesz.com/ are indispensable tools for visualizing Postgres' EXPLAIN output in a similar way. I will agree that the MSSQL query visualizer is more visually effective, though.
Re:The SQLite Index Suggester by iasay on Tuesday July 05, 2022 @03:14PM (#31992466)
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 by krylon on Tuesday July 05, 2022 @02:39PM (#31992047)
Thank you!
Re:The SQLite Index Suggester by pronoiac on Tuesday July 05, 2022 @03:25PM (#31992579)
You'd probably enjoy this other discussion on the front page: https://news.ycombinator.com/item?id=31990836
Re:The SQLite Index Suggester by abraae on Tuesday July 05, 2022 @04:28PM (#31993286)
Oracle (and likely others) have had this since well into the last century. As you might expect since it can save a huge amount of money on a large database, it's part of an (expensive) add on.
Re:The SQLite Index Suggester by SomeOther on Wednesday July 06, 2022 @05:05AM (#31998483)
Oracle can also do it autonomously if you have the super duper expensive package. It finds useful indexes based on what's queried regularly, tries them out behind the scenes to see if it'd improve things, and then creates them if they'd be useful.
Re:The SQLite Index Suggester by ayende on Wednesday July 06, 2022 @01:12AM (#31996955)
RavenDB has this feature, but it will also go ahead and create the relevant indexes needed on the fly.

It is able to do so without harming production performance.

See: https://ravendb.net/learn/inside-ravendb-book/reader/4.0/9-q...