MySQL 5 Development (Part 1) Tutorials
Database Basics / Indexes
Subtitles of the Movie (MySQL 5 Development (Part 1) / Database Basics / Indexes)
In this lesson we will briefly define the Concept and Use of an Index. An Index is an additional feature that we add to a table to help us use that table more efficiently. It becomes part of the organizational structure for a table. An Index can be used to assist in locating rows based on certain criteria as well as for ordering the rows retrieved in a result set. In more advanced courses, we will examine how to configure our indexes and our queries to get the best use from them, but for now we only need to know their basic purpose. In general an Index has two parts, the Value to be Indexed and a Pointer to the Row associated with that entry. The Pointer might be the Primary Key Value of that row or it might be an Internal Row Pointer depending on the storage engine used to manage the table. In either case, MySQL handles the internal structures of the index, we simply tell MySQL what column or columns should be used for creating the index. The contents of an index are maintained in the order implied by the data type of the column or columns on which the index is based. So if the index is based on a Character String Column, the index will be maintained in alphabetical order, if on a Number Column, then in numerical order and so on. Different storage engines offer us different indexing options, most engines offer at least B-tree Indexes which have a special internal organization that lends such an index to be especially useful in locating ranges of values, B-tree Indexes can be used with any basic data type. Some storage engines such as the Comma Separated Values Engine or the Archive Engine, did not offer indexing capabilities at all, this is not a slight on the part of the engineers who came up with these storage engines, but rather a consequence of the intended uses of these storage engines. Here's a quick breakdown of some of the special indexing capabilities offered by certain commonly used storage engines. The MyISAM engine offers FullText and GIS or geospatial indexes. FullText indexes are used for performing word searches while GIS indexes are used with a special type of data for finding locations on maps. Each of these requires columns with a specific data type; we can only have FullText indexes on character string columns and GIS indexes on spatial columns. We won't be dealing with spatial data in this course, but I thought you might want to know that it exists. The InnoDB storage engine uses Bplustree indexes, this has a more efficient internal structure than a basic B-tree index for certain operations, but there is no difference in the way we set up a basic index with this engine. This is just what it sets up for us when we say we want to create an index. The Memory Storage Engine offers us both Hash and B-tree indexes. A Hash index is more efficient for finding an exact value but is completely useless for finding ranges of values. Still, Hash is the default index type for a Memory Table, so if we want a B-tree index with this storage engine, we must specify that. The details of these Index Types are not important for now, but we will be using simple indexes before we are finished with this course so I thought you should know at least a little about the basic concept. There is one more topic I want to cover in a little more detail in this chapter. So the next lesson we'll go back to the beginning and discuss the implementation of databases by MySQL a little more thoroughly.
Tutorial Information
| Course: | MySQL 5 Development (Part 1) |
| Author: | David Swain |
| SKU: | 34225 |
| ISBN: | 1-936334-93-3 |
| Release Date: | 2011-05-27 |
| Duration: | 11 hrs / 129 lessons |
| Work Files: |
Yes |
| Captions: | No |
| Compatibility: |
Vista/XP/2000, OS X, Linux QuickTime 7, Flash 8 |
VTC Sign up & Benefits
- Unlimited Access
- 81,350 Video Tutorials (14,200 free)
- Video Available as Flash or QuickTime
- Over 715 Courses
- $30 for One Month Access
- Multi-User Discounts Available
