description: the below query can be very slow or very fast depending on an index

select .... from ... where bla_bla <not> in (select XXXX from yyy)

if tabel yyy has in index with XXXX as the leading value, this query is blazingly fast, it not and the table is significant (test case 44k rows), it is turtle slow.
I can either create an index and do the query (overall time for BOTH operations way below the original query without the index).
or
I can create another table with XXXX as the only column; populate it with (select distinct XXXX from yyy); index it; and use the new temp table for the query.
- again MUCH faster for alll of this than the original query.

---- SO

If the query optimizer sees such a query where the cardinality of the table is greater than some number (say 10) and there is no usable index,
IT SHOULD BUILD A TEMPORARY INDEX FOR THE QUERY.

=>

the below query can be very slow or very fast depending on an index

select .... from ... where bla_bla <not> in (select XXXX from YYY

if table YYY has in index with XXXX as the leading value, this query is blazingly fast,
it not and the table is significant (test case 44k rows), it is turtle slow.
I can either create an index and do the query (overall time for BOTH operations way below the original query without the index).
or
I can create another temp table with XXXX as the only column; populate it with (select distinct XXXX from YYY); index the new table on XXXX; and use the new temp table for the query.
- again MUCH faster for alll of this than the original query (seconds vs more than a minute)

---- SO

If the query optimizer sees such a query where the cardinality of the table is greater than some number (say 10) and there is no usable index,
IT SHOULD BUILD A TEMPORARY INDEX (OR LIST) FOR THE QUERY.

Perhaps an index is overkill - a simple list of distinct values (no pointers back to source record required) would be fine.

If this is done, the new temporary list or index should be kept for the duration of the transaction as it is likely that the user will follow the original query (perhaps generating a list) with another query to fix a problem (e.g. deleting orphans). Obviously if this is a list and not an index, ANY update to the original table would invalidate the list.

The extra index (list) is frequently not required and may even get in the way for normal operations, but it can make a huge difference here.

Link: This issue duplicates CORE1769 [ CORE1769 ]

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Duplicate [ 3 ]

status: Resolved [ 5 ] => Closed [ 6 ]