subhomoy Posted March 6, 2014 Share Posted March 6, 2014 hello every one... I have a database that need to be queried more than 2 lakh rows... but after quering 10,000 results or so,, the mysql is taking longer time to load.... Is there any way to upload those rows to dat database faster..... And i'm thing if it were taking so much time to load, then how much time will it take to query results from it.... Any help guys..... Thank you in advance.... Quote Link to comment Share on other sites More sharing options...
gizmola Posted March 6, 2014 Share Posted March 6, 2014 How are you loading the rows currently? Quote Link to comment Share on other sites More sharing options...
subhomoy Posted March 6, 2014 Author Share Posted March 6, 2014 I have done like this INSERT INTO ip2country (id,begin_ip,end_ip,begin_long_ip,end_long_ip,country_code,country_name,country_flag) VALUES ('','5.8.248.0','5.8.255.255','84473856','84475903','HU','Hungary','/images/ip2c/HU.jpg');INSERT INTO ip2country (id,begin_ip,end_ip,begin_long_ip,end_long_ip,country_code,country_name,country_flag) VALUES ('','5.28.0.0','5.28.7.255','85721088','85723135','HU','Hungary','/images/ip2c/HU.jpg');INSERT INTO ip2country (id,begin_ip,end_ip,begin_long_ip,end_long_ip,country_code,country_name,country_flag) VALUES ('','5.38.128.0','5.38.255.255','86409216','86441983','HU','Hungary','/images/ip2c/HU.jpg');INSERT INTO ip2country (id,begin_ip,end_ip,begin_long_ip,end_long_ip,country_code,country_name,country_flag) VALUES ('','5.56.32.0','5.56.39.255','87564288','87566335','HU','Hungary','/images/ip2c/HU.jpg');. . . . . So on.... Suppose i'have done all the uploading.. then how will i query from those thousand of results... It taking much longer time to load... Is there any easy way... I have heard about indexex but dont know...can u help me.... Quote Link to comment Share on other sites More sharing options...
sKunKbad Posted March 6, 2014 Share Posted March 6, 2014 I use ip2country too. You are way better off using the bin file. It is super fast for queries. Quote Link to comment Share on other sites More sharing options...
gizmola Posted March 7, 2014 Share Posted March 7, 2014 Yes any queries you have that are included in the where clause should have an index created on them. The only exception would be low cardinality values (for example a status flag or boolean) although those can be part of a multi-column index. As far as loading speed you can probably improve that using one or more of the suggestions provided here: http://dev.mysql.com/doc/refman/5.1/en/insert-speed.html If you're using INNODB tables you want to make sure you turn auto commit off. 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.