Re:SQLite 3.8.7 is 50% faster than 3.7.17
by mlangdon on Tuesday October 07, 2014 @07:54PM
(#8424647)
Can you expand on what you mean by index use "hints"? Or is this a feature I just haven't stumbled on yet?
Re:SQLite 3.8.7 is 50% faster than 3.7.17
by dspillett on Wednesday October 08, 2014 @04:23AM
(#8425835)
As lmz mentioned, they inform the query planner that you'd prefer it to take a particular route to address your instructions where it might have a number of choices given your current table+index structure and data patterns (and has chosen a bad one of the options in the past).
I use the quotes around the word hint because it implies that it is a fairly informal pointer that the planner might choose to ignore where in reality they usually take it as an order and go that way without question (assuming that you know better than it might otherwise chose to do, or you would not have given the hint).
Telling / forcing the query planner to use a particular index in a query e.g.:
SQL Server: http://msdn.microsoft.com/en-us/library/ms187373.aspx MySQL: http://dev.mysql.com/doc/refman/5.6/en/index-hints.html
And for the love of all that you care about, measure it with realistic data and use cases.
I've seen people optimise code while working with small test databases and making changes that are more efficient over the smaller data-sets but are hideously bad on larger ones (usually due to memory use growth, for instance hitting the limits where SQL Server starts spooling temporary data to disk instead of just keeping it in RAM for the duration of its existence).
Or just as bad: working with data that has the wrong balance and (to give another SQL example) giving index use "hints" that shave a little off the time to run over their test data but hamper the query planner on real data by making it ignore what might be better options.