Summary
This chapter is about making sure your SQL is as effective as possible from a performance point of view. Optimizers vary a lot, so use this information as a starting point, and remember, there are many other factors that can influence performance.
Make sure you understand what indexes you have.
Next, find out what tools let you look at the optimizer's choices.
Once you know your indexes and have a way of finding out whether or not they are being used in a particular query, check the WHERE clause in problem queries. There are many ways you can structure a WHERE clause to invalidate available queries. Often, you can fix a performance problem by making a simple change.
Indexes that cover queries (contain all the information you need for the results) can be very useful. There are some strict rules to follow, however, in both creating and using them.
Good joins are critical to relational database performance. Joins have many of the strictures of ordinary WHERE clauses.
Sorting always means another pass: get the data, then put it in order. Understand when you may be doing unnecessary sorting (with DISTINCT and UNION) and avoid it.
HAVING and WHERE are not the same. WHERE eliminates rows before you form groups. HAVING limits the group results. Doing WHERE work in HAVING can slow performance down.
Views are handy, but they may be expensive. Understand the cost of view queries, and create views accordingly.
Many systems provide ways to go around the optimizer in index choice. Be cautious when you do this.
Asking Performance Questions
Don't come out of this chapter feeling that you shouldn't use functions or DISTINCT or views. Instead, train yourself to look for the fastest way to achieve what you want.
|
Start by asking the big questions:
Next, get some information on the indexes.
If the optimizer is not using existing indexes, find out why.
|
|
Finally, look for ways you can speed up processing.
|