From the course: MySQL Advanced Topics (2019)
Why index? - MySQL Tutorial
From the course: MySQL Advanced Topics (2019)
Why index?
- [Instructor] An index is a small data structure that can rapidly locate an item in a larger data structure. Most databases use a form of binary tree for their indexes. A binary tree, commonly shortened to B-tree, is a simple data structure that can drastically reduce the number of comparisons necessary to find an object in a larger data structure. Indexes are used for a number of purposes. Primarily they're used for rapid lookups in large data sets. They may also be used to enforce unique and other constraints, or to provide various orders for a single data set. Indexes are not without their costs, on a table with one index, for each write operation there is at least one corresponding write operation on the index, and sometimes more. For each additional index there are more corresponding write operations, and these additional operations apply for each insert, update, and drop on each affected row. There are also storage space costs associated with each index. There are many circumstances where an index may be useful. Most tables will have a primary key, this is the column that's used to identify a row, it could be a customer number, or a part number. If you have common queries that use the ORDER BY clause, those columns could benefit from an index, or if your queries use WHERE clauses with relational comparisons, an index may improve performance of those queries. In the rest of this chapter, we'll look more specifically at how indexes work in MySQL.
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.
Download courses and learn on the go
Watch courses on your mobile device without an internet connection. Download courses using your iOS or Android LinkedIn Learning app.