SQL Performance Tuning [Electronic resources] نسخه متنی

Chapter 2. Simple Searches

In this chapter, we'll talk about syntax-based optimizing and simple search conditions.

A syntax is a choice of words and their arrangement in the SQL statement. To optimize based on syntax, assume that nonsyntactic factors (e.g., indexes, table sizes, storage) are irrelevant or unknown. This is the lowest level of optimizingit's usually predictable, and some of it can be done on the client.

There's no point in attempting to optimize most SQL syntax because only certain SQL statements have options that lend themselves to optimization. The particular syntax that offers many optimization possibilities is the SQL search condition. Here are three examples of search conditions:

... WHERE title LIKE 'The %' OR title LIKE 'A %'
... WHERE name <> 'Smith'
... WHERE number = 5

Although the slowest search conditions are those that contain joins and subqueries, this chapter deals only with single-table searches. (We'll talk about joins and subqueries later.) Also, although search conditions can appear in HAVING, IF, or ON clauses, we'll talk only about search conditions in WHERE clauses. So the chapter title"Simple Searches"is an understatement. Don't worrythe complex queries will come later.