Can adding an index make a non SARGable query SARGable?

Re:Can adding an index make a non SARGable query SARGable? by Topgamer7 on Friday October 18, 2019 @06:55PM (#21295705)

Databases can be a black box from the developer perspective. This solution might perform differently across a number of variables:

Database management solution (sql server, postgres, mysql, etc) Database version (mysql 5.6, 5.7, 5.8, mariadb, etc)

This method of finding whether your hunch works or not is the easiest alternative other than trying to read the code (or documentation, which many db engines have excellent documentation for this sort of thing).

[ Parent | on HN ]

Re:Can adding an index make a non SARGable query SARGable? by jimktrains2 on Friday October 18, 2019 @07:39PM (#21295891)

Explain and explain analyze are very powerful tools for understanding query performance.

http://www.postgresql.org/docs/9.4/static/using-explain.html

(Blog post I wrote) http://jimkeener.com/posts/explain-pg

[ Parent | on HN ]

Re:Can adding an index make a non SARGable query SARGable? by cryptonector on Saturday October 19, 2019 @01:34AM (#21296952)

It's the price you pay for having a declarative language that lets the language implementation pick from a large universe of equivalent ways to actually compile and run the code. You now have to know how to tune the thing, and that requires having either specialist knowledge of the implementation in question, or the skills to find and apply that knowledge quickly.

The value this provides is making development very agile (in the dictionary sense of the word).

[ Parent | on HN ]

Re:Can adding an index make a non SARGable query SARGable? by Pxtl on Saturday October 19, 2019 @12:50AM (#21296807)

Hah, if versions were enough for consistent performance I'd be in heaven.

I'm plagued by cases where the same schema with the same indices on same version of servers running on the same architecture gets different plans and performance.

[ Parent | on HN ]

Re:Can adding an index make a non SARGable query SARGable? by DougBTX on Sunday October 20, 2019 @04:34AM (#21302910)

For SQL Server there’s a very nice article on making query plans more predictable here: http://www.sommarskog.se/query-plan-mysteries.html

[ Parent | on HN ]

Re:Can adding an index make a non SARGable query SARGable? by wbl on Saturday October 19, 2019 @02:14AM (#21297075)

Regather the table statistics. If it was the same data I'd be shocked, but this isn't that surprising.

[ Parent | on HN ]