Effective MySQL: Optimizing SQL Statements
by Ronald Bradford
No Nonsense, Readable, Practical, and Compact
I like that this book is small; 150 pages means you can carry it easily. It’s also very no nonsense. It does not dig too deeply into theory unless it directly relates to your day-to-day needs. And those needs probably cluster heavily around optimizing SQL queries, as those pesky developers are always breaking things 😉
Jokes aside, this new book out on Oracle Press is a very readable volume. Bradford has drawn directly from real-world experience to give you the right bite size morsels you need in your day-to-day MySQL activities. Continue reading “Book Review – Effective MySQL”
Queries are so-named because of the lovely language we call SQL – simplified query language. That’s a bit of sarcasm on my part, I’ve never found it particularly simple or elegant. Profiling them involves finding how they are spending their time, and what they are asking the server to do. In this way you can make them faster, and improve performance of the whole server, and thus your website.
At any rate queries ask the database for information. If they are on the simpler side, something like give me all the users whose name starts with “hu” for example, and last name is indexed, that will run very fast. The database will lookup in the index the last name field, and find the subset of ones starting with those letters, then go lookup the records by id and return them to you. Cache the index blocks, cache the data blocks. Great! However, say those are customers, and you want their calling cellphone calling history. Ok, now you have to join on another table, matching by some key, hold all those records in memory, shuffle them around, and so on.
So queries are effectively little jobs or bits of work you ask your database server to perform on your behalf. With websites you typically have hundreds of concurrently running sessions all sending their own little SQL jobs to the server to get processed, and records returned. And blip in the radar slows everyone down, so you want them to all run quickly.
That’s where profiling comes in. MySQL, Oracle, and SQL Server alike all have EXPLAIN type facilities for showing the plan with which the database will use to fetch your data. It shows indexes, sorting, joins, order, and so forth. All of this contributes to the overall execution time and resources used on the database server.
Quora discussion by Sean Hull – What is query profiling and why is it important?