A coworker of mine just migrated our server and DB to AWS and since then a handful of queries are going extremely slow. Our code is largely PHP and we're using a MySQL database. Based on some research I've done, it seems likely that the problem could be one of two things:
1) Aurora isn't optimized to deal with older single threaded code and it might work better using MySQL RDS? (Click here to see related article)
2) All of the indexes need to be rebuilt
As I have no idea how to test or deal with either of these things (I'm a DB noob) I was hoping someone could help point me in the right direction or have some suggestions as to what's going on.
EDIT:
Here is a pastebin to some of the error logs if this is helpful: https://pastebin.com/fudCSTux, I'm trying to get that script working but no luck so far. I'm happy to paste the values from those variables, but some of them seem to be security risks, I'll have to go through and edit out the sensitive info.
EDIT 2:
Here is the pastebin for all of the database config information.
Here is the pastebin for the explain query and show create table information.
EDIT 3:
The main server is a t2 small. As far as the database goes there is a master, a reader and a writer. The reader and writers are both currently t2 mediums, vCPU 2, RAM 4GB, as far as I can tell there are no SSD/NVME devices. I'm looking at the AWS console services > rds > databases > clicked on instances > configuration > instance class
to assess the hardware.
I changed the queries and removed the quotes around driverId
and queries are still slow. I'm going to wait to index until traffic is lower this evening just in case something crashes.