一个简单的sql优化

Simple optimizations

You can sometimes use MyISAM’s COUNT(*) optimization to your advantage when you

want to count all but a very small number of rows that are well indexed. The following

example uses the standard world database to show how you can efficiently find the

number of cities whose ID is greater than 5. You might write this query as follows:

mysql> SELECT COUNT(*) FROM world.City WHERE ID > 5;

If you examine this query with SHOW STATUS, you’ll see that it scans 4,079 rows. If you

negate the conditions and subtract the number of cities whose IDs are less than or equal

to 5 from the total number of cities, you can reduce that to five rows:

242 | Chapter 6: Query Performance Optimization

mysql> SELECT (SELECT COUNT(*) FROM world.City) - COUNT(*)

-> FROM world.City WHERE ID <= 5;

This version reads fewer rows because the subquery is turned into a constant during

the query optimization phase, as you can see with EXPLAIN:

+----+-------------+-------+...+------+------------------------------+

| id | select_type | table |...| rows | Extra

|

+----+-------------+-------+...+------+------------------------------+

| 1 | PRIMARY

| City |...|

6 | Using where; Using index

|

| 2 | SUBQUERY

| NULL |...| NULL | Select tables optimized away |

+----+-------------+-------+...+------+------------------------------+

A frequent question on mailing lists and IRC channels is how to retrieve counts for

several different values in the same column with just one query, to reduce the number

of queries required. For example, say you want to create a single query that counts how

many items have each of several colors. You can’t use an OR (e.g., SELECT COUNT(color

= 'blue' OR color = 'red') FROM items;), because that won’t separate the different

counts for the different colors. And you can’t put the colors in the WHERE clause (e.g.,

SELECT COUNT(*) FROM items WHERE color = 'blue' AND color = 'red';), because the

colors are mutually exclusive. Here is a query that solves this problem:16

mysql> SELECT SUM(IF(color = 'blue', 1, 0)) AS blue,SUM(IF(color = 'red', 1, 0))

-> AS red FROM items;

And here is another that’s equivalent, but instead of using SUM() uses COUNT() and en-

sures that the expressions won’t have values when the criteria are false:

mysql> SELECT COUNT(color = 'blue' OR NULL) AS blue, COUNT(color = 'red' OR NULL)

-> AS red FROM items;