There are a lot of ways to speed up your DB like adding indexes, sharding, and not doing joins on joins on joins but let’s start with some super basic redflags I look for before making any advanced changes. Here are two commands that I find to be some of the biggest contributors to high latency DB queries:
I would steer away from querying on string fields if possible, especially with wildcards. Using the LIKE wildcard query pretty much forces the DB to regexp on every single row on the table. Even adding an index on this will still not get great results.
The DISTINCT keyword seems innocent enough until you realize that in order to ensure all records with that field are distinct the DB has to check every record it is returning against every other record it is returning. Depending on your record set this is a lot of CPU that will be used to service this request.
If you can think of any others please send them my way so I can add them to the collection.
Also if you are using an ORM check out my post Is Your ORM Making Your Site Slow?.
For more of the advanced ways to speed up your page load time while keeping costs down please download my free e-book 20 Things You Can Do Today To Save Money On Your Amazon Web Services Bill
Signup for the mailing list