For an existing table in our database,
if I create an index on a table such as:
create index i_system_code_system_type_no on system_code (SYSTEM_TYPE_NO);
and then set statistics:
set statistics index i_system_code_system_type_no;
when executing a query against the table which "should" use the index, doesn't. For whatever reason, the optimizer I'm guessing (unless I've missed something), doesn't feel it's the best approach. However, if I restart the Firebird Guardian service, it does use it.
Does it take time for the server to catch up and build the index? Short of me forcing the index to use in the actual query itself is there a way to update or do something else to tell the server to use my new index? I can tell its using the new index because the "Adapted Plan" shows it using the index, plus the query execution time drops from about 0.5 sec to "instant".
This new index will be applied to 00's of databases of our customers, so just trying to determine the best way to distribute this update without having to restart the individual services on each of the customers machines.