MySQL Forums
Forum List  »  Optimizer & Parser

Re: two column int index not used correctly
Posted by: Jørgen Løland
Date: June 17, 2011 01:34AM

Hi Ming,

Thinking of it, there is actually a possibility for MySQL to consider range access also in non-constant cases. Take a look here:

http://dev.mysql.com/doc/refman/5.5/en/explain-output.html (See "Range checked for each record")

Can you modify case 3) like this and report back with the results?

EXPLAIN 
SELECT
    A.*,
    ip_range_table.ip_class
FROM web_query.web_query_ip_dest_analysis_20110616171614564_merged AS A
    JOIN ip_range_table FORCE INDEX (idx_start_ip) ON A.ip>=ip_range_table.start_ip;

("LEFT" was removed)

Hope this helps,
Jørgen Løland
Software Engineer, MySQL, Oracle
jorgenloland.blogspot.com

Options: ReplyQuote




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.