mysql optimized query execution

Question

As part of an ongoing research work, I am checking if an URL exists or not using the cURL command. I have been executing a shell script for couple of days and it is doing some updates for each URL in my database. However, the script seems to update around only 100,000 rows in a day.

I was thinking if I could write the values in a file first and then do the updates, the execution might be faster.

I am connecting to the database using the command line.

mysql -h servername -u username -ppassword databasename "Update Query"

For example, instead of connecting to the database 2 million times like above from the command line and updating 2 million rows, I am planning to connect to the database only once from the command line and update 2 million rows from the file.

So is the second approach better than the first one or the time difference would be negligible?

Answer 1

Three approaches.

You could using load data infile

You could build up a .sql file with all of the updates you need.

You could use something other than a CLI to connect to the URLs and DB. In other words, not using "curl" and "mysql" commands, but using a real programming language and provided libraries for checking URLs and updating databases.

Any of those would probably be faster. Though you'll likely get more speed improvement by making the http calls in parallel. You can do that more easily with a real programming language.