Learning Mysql
-> firstname = "Rose" AND
Download 4.24 Mb. View original pdf
|
- Navigate this page:
- Creating Tables | 207
| -> firstname = "Rose" AND -> secondname = "Elizabeth"; However, the index can’t be used for this query because the leftmost column in the index, firstname , does not appear in the query: mysql> SELECT * FROM customer WHERE -> surname = "Williams" AND -> secondname = "Elizabeth"; The index should help narrow down the set of rows to a smaller set of possible answers. For MySQL to be able to use an index, the query needs to meet both the following conditions. The leftmost column listed in the KEY (or PRIMARY KEY) clause must be in the query. The query must contain no OR clauses for columns that aren’t indexed. Again, you can always use the EXPLAIN statement to check whether an index can be used fora particular query. Before we finish this section, here area few ideas on how to choose and design indexes. When you’re considering adding an index, think about the following Indexes cost space on disk, and they need to be updated whenever data changes. If your data changes frequently, or lots of data changes when you do make a change, indexes will slow the process down. However, in practice, since SELECT statements Creating Tables | 207 data reads) are usually much more common than other statements (data modifications, indexes are usually beneficial Only add an index that’ll be used frequently. Don’t bother indexing columns before you see what queries your users and your applications need. You can always add indexes afterward If all columns in an index are used in all queries, list the column with the highest number of duplicates at the left of the KEY clause. This minimizes index size The smaller the index, the faster it’ll be. If you index large columns, you’ll get a larger index. This is a good reason to ensure your columns areas small as possible when you design your tables For long columns, you can use only a prefix of the values from a column to create the index. You can do this by adding a value in parentheses after the column definition, such as KEY names (firstname(3), secondname(2), surname. This means that only the first three characters of firstname are indexed, then the first two characters of secondname , and then 10 characters from surname. This is a significant saving over indexing 50 characters from each of the 3 columns When you do this, your index will be less able to uniquely identify rows, but it’ll be much smaller and still reasonably good at finding matching rows. Download 4.24 Mb. Share with your friends:
|