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.... Link to comment https://forums.phpfreaks.com/topic/286766-load-sql-database-faster/ Share on other sites More sharing options...
gizmola Posted March 6, 2014 Share Posted March 6, 2014 How are you loading the rows currently? Link to comment https://forums.phpfreaks.com/topic/286766-load-sql-database-faster/#findComment-1471663 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.... Link to comment https://forums.phpfreaks.com/topic/286766-load-sql-database-faster/#findComment-1471666 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. Link to comment https://forums.phpfreaks.com/topic/286766-load-sql-database-faster/#findComment-1471670 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. Link to comment https://forums.phpfreaks.com/topic/286766-load-sql-database-faster/#findComment-1471808 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.