Is there any way to instruct MySQL to NOT use an index even if it's available? Similarly, to not use a column even if it's available? (For instance, just error the query with "column does not exist".) It would be useful to have this capability when preparing to drop an index or a fields.
How about Postgres?
Disclaimer: PostgreSQL only... haven't used MySQL in over 8 years.
Also, you didn't seem to be targeting queries, but I thought I would offer it up anyway (maybe for anyone with a similar question).
This is a bit of a hack, but if you consider the following table:
create table foo (
id integer
);
create index foo_ix1 on foo (id);
insert into foo generate_series (1, 1000000);
This, as you might expect, will use the index:
explain
select * from foo
where id between 5 and 10;
Unless you have a function-based index for that specific function, applying ANY function to the column should make it impossible for the planner to use the index. Simple example:
explain
select * from foo
where id + 0 between 5 and 10;
I haven't changed the results, but I forced a full scan.
For text columns, for example, you can just append an empty string: text_field || ''.
In PostgreSQL you can turn off the index scan:
set enable_indexscan to 'off';
set enable_indexonlyscan to 'off';
set enable_bitmapscan to 'off';
Or make them very costly:
set random_page_cost to 10000000;