tailieunhanh - MySQL Enterprise Solutions phần 7

hoặc nó quét toàn bộ bảng và không thể loại bỏ bất kỳ hàng nào bằng cách đánh giá biểu hiện trong WHERE khoản. phạm vi kiểm tra cho mỗi bản ghi (bản đồ chỉ số: #): Điều này có nghĩa rằng MySQL đã không tìm thấy một chìa khóa để tìm kiếm các hàng trong bảng này; | L232 Table Design NOT NULL salary MEDIUMINT UNSIGNED NOT NULL key lname_fname lname fname key position_salary position salary key hire_date hire_date As a rule of thumb when you re creating a key you should try to accomplish more than one goal. You do not want to create a key that will optimize just one query that is run infrequently. You want to try to pick a key in such a way that it will also help other queries. In our previous example instead of creating a lname key we used lname fname . We assumed that we would do a lookup on the combination of last name and first name much more frequently than just the last name. If the majority of the lookups used only the last name the lname key would be more preferable because it would require less space to store and would also be used more efficiently by the optimizer. The same concept applies to the position salary key. We chose the hire_date key because we assumed that when we want to do a lookup by hire date there is rarely any other information available to use that we can include in the query to narrow down the range of records. If this were not the case we would have considered adding other columns. Note the order of columns in the key. It is very important. The first prefix column or columns in a key must be the ones for which we always know the value or the range for the queries that we would like to optimize. The subsequent suffix columns are the ones for which we may or may not have the value or the range. For example when we decided to create the key on position salary we did so assuming that we always know the position and that sometimes we know the salary. If we reversed the order of the fields in the key we would be able to use that key only for queries where the salary value or range is known. One good approach is to begin with a few self-evident keys and then add more as needed. An effective technique in determining the needed keys is to enable log-long-format and log-slow-queries options on the server and