mysql> show variables like "%trace%";      

mysql> set optimizer_trace="enabled=on",end_markers_in_json=on;

Query OK, 0 rows affected (0.00 sec)

mysql> set optimizer_trace_max_mem_size=1000000;

Query OK, 0 rows affected (0.00 sec)

mysql> select rental_id from rental where 1=1 and rental_date >= '2005-05-25 04:00:00' and rental_date <='2005-05-25 05:00:00'  and inventory_id=4466;

+-----------+

| rental_id |

+-----------+

|        39 |

+-----------+

1 row in set (0.00 sec)


mysql>

mysql> select * from information_schema.optimizer_trace\G


mysql> alter table rental add index idx_rental_date (rental_date,inventory_id,customer_id);

Query OK, 0 rows affected (0.48 sec)

Records: 0  Duplicates: 0  Warnings: 0


mysql> 

查看索引使用的好坏:

mysql> show status like "Handler_read%";

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| Handler_read_first    | 3     |

| Handler_read_key      | 8     |

| Handler_read_last     | 0     |

| Handler_read_next     | 16059 |

| Handler_read_prev     | 0     |

| Handler_read_rnd      | 0     |

| Handler_read_rnd_next | 16337 |

+-----------------------+-------+

7 rows in set (0.00 sec)


mysql