MySQL Forums
Forum List  »  Optimizer & Parser

Re: How can I optimize this query?
Posted by: Rick James
Date: March 13, 2010 04:07PM

What you have (a little more readable):
SELECT  gi.longip, cl.locId,
        cl.city, cl.region,
        count(*) as loccount
    FROM  
       ( SELECT  distinct longip
           from  whyu.stats
           WHERE  longip!=0
       ) as gi
    STRAIGHT_JOIN geoipdb.cityblocks AS cb  ON g.longip BETWEEN cb.startIpNum AND endIpNum
    INNER JOIN  geoipdb.citylocation AS cl  ON cb.locId = cl.locId
    GROUP BY  cl.locId

Are there any overlaps in start/endIpNum? If not, we may be able to turn this Order(N*N) STRAIGHT_JOIN into an Order(N) subquery.

To assist in analyzing slow SELECTs, please provide
* SHOW CREATE TABLE tbl\G -- engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]

See if any of these help:
http://forums.mysql.com/read.php?10,356125 (update big table cause an excution time out)
http://forums.mysql.com/read.php?10,293341 (Help with the best table layout for message board)
http://forums.mysql.com/read.php?10,278476 (Can a Mysql MyISAM or InnoDB table benefit from the STRIPE feature of disk device?)
http://forums.mysql.com/read.php?10,278480 (Can a Mysql MyISAM or a InnoDB table benefit from a set of multi-thread and multi-disk I/O device?)
http://forums.mysql.com/read.php?52,271268 (Checking for exact match in row)
http://forums.mysql.com/read.php?10,255092 (What data type should I use and what storage engine?)

Are you willing to break geoipdb.cityblocks into 32 tables?

Options: ReplyQuote


Subject
Views
Written By
Posted
5243
March 12, 2010 02:40PM
Re: How can I optimize this query?
2318
March 13, 2010 04:07PM
1747
March 13, 2010 04:17PM
1913
March 16, 2010 08:22AM
2377
April 14, 2010 02:27AM
1838
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.