MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
N.M.P.L. Company www.NearMePayday.Loan || 3-month (90 days)
Powered by MySQL
 
Home / Indexes / Question No: 135

What is index terminology or concept and how does index work in MySQL?

I am new to MySQL and need to learn more about this database. I want to know about MySQL indexes. Can you explain, what is index terminology or concept in MySQL? Also, why the indexes are needed to speed up select queries?

Answer No: 135

An INDEX is essentially an ordered (or indexed) subset of table columns, with each row entry pointing to its corresponding table row.

We index the specific columns to keep them in organized versions. An indexed column just provides a sorted data structure so that some searches on that column could be faster. For example, a telephone book. It's in sorted order by name. So it makes it a lot faster to find someone by name. So a telephone book is a form of an index.

MySQL allows to create indexes to facilitate quick retrieval of desired data. Using indexes, MySQL can jump directly to the wanted record. Without using index(s), MySQL has to read the entire data file to find the required record(s). In short, with INDEX select queries work considerably faster and without INDEX such queries works slow. Take an example, create a table called "buyers":

CREATE TABLE buyers(buyer_id INT NOT NULL, buyer_name CHAR( 50 ) NOT NULL );
Then insert 1000 buyers with different names into the table in a completely non-alphabetic order. A small portion of the data file may be represented like below:
buyer_id buyer_name
1 Zeshan
2 Aqeel
3 Nick
[...] [...]
998 Lara
999 Barbara
1000 Ken

By above there seems no recognizable order to the "buyer_name" column. However, if we create an index on the "buyer_name" column, MySQL will automatically order this column's data alphabetically:

buyer_name
Aqeel
Barbara
Ken
Lara
Nick
[...]
Zeshan

In case of indexed column, for each data entry, MySQL also internally maintains a "pointer" to the inserting row in the actual data file. So if I want to get the buyer_id of Lara like

SELECT buyer_id FROM buyers WHERE buyer_name='Lara';

MySQL can jump directly to the correct row in the data file using buyer_name index "pointer", and return the value of buyer_id (998). So with the index MySQL only has to look at one row to get the wanted result. Without an index on "buyer_name", MySQL would’ve scanned all 1000 rows in the data file to find the wanted buyer_name 'Lara'. In other words, with the index(s) MySQL has to evaluate very less rows to respond the query.

In short, indexes are one of the keys to get speedy responses in large databases. It does not matter how simple the table is, the scan will never be fast when a table has 500000 rows. If a dynamic site has 500000 rows table, the developer should really analyze possible indexes and possibly has to rewrite queries to optimize the application.

Related MySQL FAQs to the Above FAQ

How-to-get-existing-indexes-of-the-table-in-MySQL How to get existing indexes of the table in MySQL?

How-many-types-of-indexes-or-keys-are-in-MySQL How many types of indexes or keys are in MySQL?

What-is-single-column-index-or-key-in-MySQL What is single column index or key in MySQL?

What-is-multi-column-index-or-key-in-MySQL What is multi column index or key in MySQL?

What-is-partial-column-and-prefixed-column-index-or-key-in-MySQL What is partial-column and prefixed-column index or key in MySQL?

When-does-multi-column-index-come-into-use-in-MySQL When does multi column index come into use in MySQL?

What-are-best-practices-to-pick-columns-to-index What are best practices to pick columns to index?

What-are-advantages-and-disadvantages-of-indexes-in-MySQL What are advantages and disadvantages of indexes in MySQL?

How-to-remove-or-drop-indexes-in-MySQL How to remove or drop indexes in MySQL?

Is-it-possible-to-apply-more-than-one-keys-on-a-single-column Is it possible to apply more than one keys on a single column?

What-is-foreign-key-in-MySQL What is foreign key in MySQL?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2021  www.mysqlfaqs.net
All rights reserved.