I am an informix newbie. I am using server studio for connecting to IDS 11. I have a query that searches by name. it is a wild card search. for example, if I search on 'John doe' it will result in 'John Doe*'. we have about 120K rows in the table. currently the search query runs quite slowly and I need to fine tune the same. my question is when the search is based on wild card, will the index be applied for the search? how can I run the explain plan to decipher this information?
I do not know whether you can switch it on via Serverstudio, but you can try one of the following:
- identify the session in "onstat -g ses"
- run "onmode -Y <sessionid> 1 <filename>" for that session to capture the plan (look in the working directory for it)
create a stored procedure like:
create procedure "<username>".sysdbopen()
set explain file to <file>;
set explain on;
So, every time the user logs in, a new plan will be created.
Normally, if the column is indexed and the statistics a up to date, he should use the index, but only if the wildcard is not at the beginning of the string.