For the past week I’ve been banging my head against the wall trying to figure out why my MySQL database is so slow performing UPDATEs and DELETEs. Single UPDATE or DELETE statements aren’t so bad, but whenever I try to delete many entries, say 30,000 or so rows, things just grind to a halt!

Well this is what I’ve figured out: Oracle’s mysql.connector library for Python is extremely slow at doing UPDATEs and DELETES. While mysql.connector handles SELECT statements just fine, and can perform INSERTs blazing fast as well when using the executemany() method, the library is just awful at doing bulk UPDATEs and DELETEs.

It kind of makes sense that this might be the case, as MySQL has long been associated with PHP. PHP and MySQL are like peanut butter and jelly. Likewise, when you think about Node.js, MongoDB seems to be the preferred database of choice for backend JavaScript programming. Python and MySQL? That combo just doesn’t seem to go well together from what I have found.

Personally, I’ve decided that the best solution for my needs is to simply move my data from MySQL over to a new PostgreSQL database, not because MySQL is slow, but because I know that PostgreSQL has an excellent Python library: psycopg2

If you happen to find yourself in a similar situation, struggling to get MySQL to play nicely with Python, I suggest you dump the dolphin and unleash the Psycho Pig!

UPDATE (January 3rd, 2020… 1 Day Later): After finishing moving all of my data over from MySQL to PostgreSQL, I found that using Python + PostgreSQL + psycopg2 has completely eliminated my problem with painfully slow UPDATEs and DELETEs. If you find yourself running into a similar problem with Python, MySQL, and mysql.connector, I highly recommend switching over to Python/PostgreSQL/psycopg2. However, you need to make sure that you are using psychopg2’s execute_batch() method in order to see these performance gains. Again, make sure to use psycopg2’s execute_batch() method when doing BULK UPDATEs, DELETEs, or INSERTs.