Consider Full Text Search for Name/Description of the Packages table
Queries such as these, do a full table scan to find a matching Name Of Description. These queries in volume seem to take our aur instance down generating ~ 600% mariadbd load.
explain SELECT Packages.ID,Packages.Name,PackageBases.ID AS PackageBaseID,PackageBases.Name AS PackageBase,Version,Description,URL,NumVotes,Popularity,OutOfDateTS AS OutOfDate,Users.UserName AS Maintainer,SubmittedTS AS FirstSubmitted,ModifiedTS AS LastModified FROM Packages LEFT JOIN PackageBases ON PackageBases.ID = Packages.PackageBaseID LEFT JOIN Users ON PackageBases.MaintainerUID = Users.ID WHERE (Packages.Name LIKE '%gstreamer0.10%' OR Description LIKE '%gstreamer0.10%') AND PackageBases.PackagerUID IS NOT NULL LIMIT 5000;
Note there might be more LIKE queries which I've missed. But the most notable onces are done via the RPC interface.
https://dzone.com/articles/how-to-optimize-mysql-queries-for-speed-and-perfor