html5-img
1 / 12

Chapter 5 Query Optimization

Chapter 5 Query Optimization. indexes. For a MyISAM table, the table's data rows are kept in a data file, and index values are kept in an index file.

ronald
Télécharger la présentation

Chapter 5 Query Optimization

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 5Query Optimization Mysql Developer"s Library Paul Dubios 4th Edition

  2. Mysql Developer"s Library Paul Dubios 4th Edition

  3. Mysql Developer"s Library Paul Dubios 4th Edition

  4. indexes • For a MyISAM table, the table's data rows are kept in a data file, and index values are kept in an index file. • The InnoDB storage engine does not separate data rows and index values in the same way, although it does maintain indexes as sets of sorted values • Indexes are even more valuable when you're running queries involving joins on multiple tables. Mysql Developer"s Library Paul Dubios 4th Edition

  5. How MySQL uses indexes • Indexes are used to speed up searches for rows matching terms of a WHERE clause or rows that match rows in other tables when performing joins. • For queries that use the MIN() or MAX() functions, the smallest or largest value in an indexed column can be found quickly without examining every row. • MySQL can often use indexes to perform sorting and grouping operations quickly for ORDER BY and GROUP BY clauses. Mysql Developer"s Library Paul Dubios 4th Edition

  6. How MySQL uses indexes • Sometimes MySQL can use an index to read all the information required for a query. • Suppose that you're selecting values from an indexed numeric column in a MyISAM table, and you're selecting no other columns from the table. In this case, when MySQL reads an index value from the index file, it obtains the same value that it would get by reading the data file. There's no reason to read values twice, so the data file need not even be consulted. Mysql Developer"s Library Paul Dubios 4th Edition

  7. Cost of Indexing • First, indexes speed up retrievals but slow down inserts and deletes, as well as updates of values in indexed columns. • Second, an index takes up disk space, and multiple indexes take up correspondingly more space. This might cause you to reach a table size limit more quickly than if there are no indexes. Mysql Developer"s Library Paul Dubios 4th Edition

  8. Choosing Indexes • Index columns that you use for searching, sorting, or grouping, not columns you select for output. • The best candidate columns for indexing are the columns that appear in your WHERE clause, columns named in join clauses, or columns that appear in ORDER BY or GROUP BY clauses. Mysql Developer"s Library Paul Dubios 4th Edition

  9. Choosing Indexes • Consider column cardinality. The cardinality of a column is the number of distinct values that it contains. • For example, a column that contains the values 1, 3, 7, 4, 7, and 3 has a cardinality of four. • Indexes work best for columns that have a high cardinality relative to the number of rows in the table. Mysql Developer"s Library Paul Dubios 4th Edition

  10. Choosing Indexes • Index short values. • Index prefixes of string values. • If you're indexing a string column, specify a prefix length whenever it's reasonable to do so. • if you have a CHAR(200) column, don't index the entire column if most values are unique within the first 10 or 20 characters. Indexing the first 10 or 20 characters will save a lot of space in the index, and probably will make your queries faster as well. Mysql Developer"s Library Paul Dubios 4th Edition

  11. Choosing Indexes • Take advantage of leftmost prefixes. When you create an n-column composite index, you actually create n indexes that MySQL can use. A composite index serves as several indexes because any leftmost set of columns in the index can be used to match rows. Such a set is called a "leftmost prefix.“ • Suppose that you have a table with a composite index on columns named state, city, and zip. Rows in the index are sorted in state/city/zip order, so they're automatically sorted in state/city order and in state order as well. Mysql Developer"s Library Paul Dubios 4th Edition

  12. Choosing Indexes • Don't over-index • Every additional index takes extra disk space and hurts performance of write operations, as has already been mentioned. Indexes must be updated. • Match index types to the type of comparisons you perform. Mysql Developer"s Library Paul Dubios 4th Edition

More Related