The last couple weeks I’ve had to focus on building and optimizing MySQL select statements. It’s been an interesting journey as I’ve learned about choosing optimal field types when building tables, when to use a subquery over a left join, and how to use the information procedure analyse() gives. But the one thing I did not expect was the extreme performance increase LIMIT gives when used appropriately. Looking back, I should have guessed it, since when you limit a query to only retrieving a specified amount of rows instead of an indefinite amount, MySQL has to do a lot less work.
Today’s challenge though is a little different; I’m working with an existing table which has text fields as indexes along with integer indexes and primary key. This quarter million row table needs to be checked against for data, but I am only able to check against indexes on varchar(80) fields and not unique keys, so those queries are running orders of magnitude slower than the others. Unfortunately, this data is used in many other locations, so I am not able to do anything with it right now.