MySQL Forums
Forum List  »  Optimizer & Parser

Re: How can I optimize this query?
Posted by: Rick James
Date: March 27, 2010 03:36PM

See if this is closer to finding all the results in a single query:

SELECT cl.country, cl.region, cl.city
    FROM ( SELECT
                ( SELECT endIpNum
                    FROM geoipdb.CityBlocks
                    WHERE endIpNum >= o.longip
                    ORDER BY endIpNum
                    LIMIT 1
                )  AS endIpNum
            FROM ( SELECT distinct longip FROM whyu.stats ) o
          ) a
    JOIN geoipdb.CityBlocks cb    ON cb.endIpNum = a.endIpNum
    JOIN geoipdb.CityLocation cl  ON cl.locId = cb.locId;
This requires a UNIQUE index on cb.endIpNum.

Options: ReplyQuote


Subject
Views
Written By
Posted
5256
March 12, 2010 02:40PM
2322
March 13, 2010 04:07PM
1750
March 13, 2010 04:17PM
1916
March 16, 2010 08:22AM
2381
April 14, 2010 02:27AM
Re: How can I optimize this query?
1841
March 27, 2010 03:36PM


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.