sangoku Posted August 14, 2010 Share Posted August 14, 2010 Hy guys I have a bit of an issue with instaling and optimising a GIANT table.... most of you know the GeoIp database... well im trzing to optimise so it can run on mysql the job giver is to stingy to pay the binary version.... so i ahve tor esort to the CVS version of it... i imported it and it is slowwwwwww How can i speed it up? CREATE TABLE IF NOT EXISTS `GeoIpBlocks` ( `startIpNum` INT(10) UNSIGNED NOT NULL , `endIpNum` INT(10) UNSIGNED NOT NULL , `locId` INT(10) UNSIGNED NOT NULL , PRIMARY KEY (`startIpNum`, `endIpNum`) ) ENGINE = MyISAM It has 300k+ rows.... I know i need ti make it partitioned but on what basis? I think range is best.... here is an example of the content of the table 16777216 17301503 17 17367040 17432575 153 17435136 17435391 17 17498112 17563647 119 17563648 17825791 49 17825792 18087935 119 18153472 18219007 111 18219008 18350079 103 18350080 18874367 49 18939904 19005439 111 19005440 19136511 218 19136512 19202047 94 19267584 19398655 103 19398656 19726335 17 19726336 19791871 49 19791872 19922943 209 19922944 20054015 49 20054016 20058111 2 The only query which will run on this table is SELECT locId FROM GeoIpBlocks WHERE some_int_value BETWEEN startIpNum AND endIpNum; Anz advise wuold be apriciated. Quote Link to comment Share on other sites More sharing options...
fenway Posted August 14, 2010 Share Posted August 14, 2010 300K rows is nowhere near giant -- don't even worry about partitioning at this level. Quote Link to comment Share on other sites More sharing options...
sangoku Posted August 15, 2010 Author Share Posted August 15, 2010 It is giant, dont know the exact number but a query takeas about 1 s... and i have very fast hdd Quote Link to comment Share on other sites More sharing options...
fenway Posted August 15, 2010 Share Posted August 15, 2010 Then blame the query, not the table. Quote Link to comment Share on other sites More sharing options...
sangoku Posted August 15, 2010 Author Share Posted August 15, 2010 Do you have any advice or...? The query is stated on the end of the 1st post... It can not be simpler... The table is almost full range of int(10) +/- 20% so yes that is many how ever you turn it... And I am asking if anyone knows how to organize the index and the partitioning. The partitioning is as you told not so necessary because i have only 3 columns with a integer in it.... But then again i am inserting a 130 mb CSV in it... and to have at least around 150mb table always read in when a query takes place is not the definition of efficiency i have in mind... Quote Link to comment Share on other sites More sharing options...
fenway Posted August 15, 2010 Share Posted August 15, 2010 Let's see the EXPLAIN output -- see my sig. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.