MySQL Forums
Forum List  »  Optimizer & Parser

not able to use index, seems optimizer forces not to use index,
Posted by: yi li
Date: September 21, 2011 08:08AM

i encountered a problem with using index in query and will apperiate if anyone could help.
in summary, mysql refuse to use index when the valued compared to the index (the right operand) is a value returned from a function;

What i have:
I have the table in mysql 5.0; table name is nsd_ecomm; index column is 'occurance'.
And the table is growing in rows every day. The occurance is actually a timestamp formated in '20110919000000' fromat.

the index works for the query below:
select * from nsd_ecomm where occurance > '20110501' and occurance < '20110502';
The query plan shows this is a range search for the one day data.

my objective

I want to write a query/store_procedure which will count the rows for the last 10 days.
So I need to generate a timestamp from sysdate() function, decrement by 10 days and then convert it to string to compare with the index column.

I prove the function: date_format((sysdate() - interval 10 day),'%Y%m%d') should provide a timestamp 10 days ago in the format specified.

so my query is:
select * from nsd_ecomm where occurance > quote(date_format((sysdate() - interval 10 day),'%Y%m%d'))

the problem:
in theory, the query above should continue to use the 'occurance' as index by comparing with the value generated from the function.
In reality, the index is not used and the query always result in full table search.

Here is what i got from the query plan.
the sql engine always need to apply a 'convert' function to the value returned from the function, which seems to nulify the use of index.

select `security_file`.`nsd_ecomm`.`asp_session_id` AS `asp_session_id`,`security_file`.`nsd_ecomm`.`server_id` AS `server_id`,`security_file`.`nsd_ecomm`.`occurance` AS `occurance`,`security_file`.`nsd_ecomm`.`site_code` AS `site_code`,`security_file`.`nsd_ecomm`.`owner` AS `owner`,`security_file`.`nsd_ecomm`.`event_id` AS `event_id`,`security_file`.`nsd_ecomm`.`ip_address` AS `ip_address`,`security_file`.`nsd_ecomm`.`payment_number` AS `payment_number`,`security_file`.`nsd_ecomm`.`email_address` AS `email_address`,`security_file`.`nsd_ecomm`.`subsystem_transaction_id` AS `subsystem_transaction_id`,`security_file`.`nsd_ecomm`.`subsystem_status` AS `subsystem_status`,`security_file`.`nsd_ecomm`.`subsystem_result` AS `subsystem_result`,`security_file`.`nsd_ecomm`.`subsystem_verbose` AS `subsystem_verbose`,`security_file`.`nsd_ecomm`.`activity_id` AS `activity_id`,`security_file`.`nsd_ecomm`.`code` AS `code` from `security_file`.`nsd_ecomm` where (`security_file`.`nsd_ecomm`.`occurance` > convert(quote(date_format((sysdate() - interval 1 day),'%Y%m%d')) using latin1))

what I tried to fix and all failed:
1 wrote a stored procedure which runs the functions and return the value as a string; and then use the returned string in the 'where' clause to try to use index
the result is a full table search
2. try force using index with 'use index'
3. wrap the function with convert() function, the same way as the sql engine did
it is still a full table search
the sql engine explains the query plan as:
select `security_file`.`nsd_ecomm`.`asp_session_id` AS `asp_session_id`,`security_file`.`nsd_ecomm`.`server_id` AS `server_id`,`security_file`.`nsd_ecomm`.`occurance` AS `occurance`,`security_file`.`nsd_ecomm`.`site_code` AS `site_code`,`security_file`.`nsd_ecomm`.`owner` AS `owner`,`security_file`.`nsd_ecomm`.`event_id` AS `event_id`,`security_file`.`nsd_ecomm`.`ip_address` AS `ip_address`,`security_file`.`nsd_ecomm`.`payment_number` AS `payment_number`,`security_file`.`nsd_ecomm`.`email_address` AS `email_address`,`security_file`.`nsd_ecomm`.`subsystem_transaction_id` AS `subsystem_transaction_id`,`security_file`.`nsd_ecomm`.`subsystem_status` AS `subsystem_status`,`security_file`.`nsd_ecomm`.`subsystem_result` AS `subsystem_result`,`security_file`.`nsd_ecomm`.`subsystem_verbose` AS `subsystem_verbose`,`security_file`.`nsd_ecomm`.`activity_id` AS `activity_id`,`security_file`.`nsd_ecomm`.`code` AS `code` from `security_file`.`nsd_ecomm` where (`security_file`.`nsd_ecomm`.`occurance` > convert(date_format((sysdate() - interval 1 day),'%Y%m%d') using latin1))

Options: ReplyQuote

Written By
not able to use index, seems optimizer forces not to use index,
September 21, 2011 08:08AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.