Thanks Caleb for bringing it up. When I opened this issue, I originally wanted to propose the following syntax: 1、When we want to force the selection of certain indexes: SELECT ...USE INDEX (ks.idx1, ks.idx2....) ... FROM ...WHERE ... or SELECT .../*+ USE INDEX (ks.idx1, ks.idx2....)*/ ... FROM ...WHERE ...
2、When we want to ignore the selection of some indexes: SELECT ...IGNORE INDEX (ks.idx1, ks.idx2....) ... FROM ... WHERE ... or SELECT .../*+ IGNORE INDEX (ks.idx1, ks.idx2....) */ ... FROM ... WHERE ... And In fact, what I originally wanted to do was to provide a hint function <https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html#optimizer-hints-overview> similar to MySQL, which can push down some rules dynamically. The work to provide a hint for DDL needs to be accomplished in many steps, CASSANDRA-18112 for index hint is part of it, maybe the first step. Ekaterina Dimitrova <e.dimitr...@gmail.com> 于2024年12月21日周六 22:27写道： > Naming is hard but to me providing what Caleb mentioned through something > like WITH OPTIONS sounds reasonable. Thanks for bringing it up. > > On Sat, 21 Dec 2024 at 2:46, Joel Shepherd <sheph...@amazon.com> wrote: > >> WITH INDEX (or something equivalent) seems really useful. >> >> Less opinionated on the specific syntax, but I think there is a lot of >> value in the form of predictable, controllable performance, in giving >> developers more direct control over query execution, whether that's >> index selection or even lower-level decisions. If you've experienced the >> thrill of operating a database with a cost-based planner that abruptly >> selects a new, sub-optimal plan due to a change in statistics or >> configuration, you'll appreciate language features that yield some >> planning control back to you. It does increase the burden on the >> developer to understand how best to execute the query, but it makes >> their intent much more obvious, and easier to adjust as the system >> changes. >> >> -- Joel. >> >> On 12/20/2024 12:28 PM, Caleb Rackliffe wrote: >> > Some of your are probably familiar with work in the DS fork to improve >> > the selection of indexes for SAI queries in >> > >> https://github.com/datastax/cassandra/commit/eeb33dd62b9b74ecf818a263fd73dbe6714b0df0#diff-2830028723b7f4af5ec7450fae2c206aeefa5a2c3455eff6f4a0734a85cb5424. >> >> > >> > >> > While I'm eagerly anticipating working on that in the new year, I'm >> > also wondering whether we think some simple CQL extensions to manually >> > control index selection would be helpful. Maxwell proposed this a >> > while back in CASSANDRA-18112, and I'd like to propose a syntax: >> > >> > >> > ex. Do not use the specified index during the query. >> > >> > SELECT ... FROM ... WHERE ... WITHOUT INDEX <ks.idx> >> > >> > This could be helpful for intersection queries where one of the >> > provided clauses is not very selective and could simply be handled via >> > post-filtering. >> > >> > ex. Require the specified index to be used. >> > >> > SELECT ... FROM ... WHERE ... WITH INDEX <ks.idx> >> > >> > This could be helpful in scenarios where multiple indexes exist on a >> > column and was the primary motivation for CASSANDRA-18112. >> > >> > Thoughts? >> >
