CODEWITHSUNDEEP
phpmysqlsql
Kanishka Panamaldeniya
Kanishka Panamaldeniya

Reputation: 17566

How the mysql indexing makes SELECT queries faster

hi i know about MySQL Indexes to some extent.

i can create a index when creating a table by

CREATE TABLE Product (
    ->    ID SMALLINT UNSIGNED NOT NULL,
    ->    ModelID SMALLINT UNSIGNED NOT NULL,
    ->    PRIMARY KEY (ID),
    ->    INDEX (ModelID)

add an index after create table by 

CREATE INDEX index_name
ON table_name (column_name)

also alter the index by

ALTER TABLE TABLE_NAME ADD INDEX (COLUMN_NAME);

**what I want to know is how indexing makes the select query fast

how does indexing affects to the select algorithm of MySQL , how the indexed columns are affecting to the algorithm ? please explain , thanks in advance

Upvotes: 2

Views: 1935

Answers (2)

Chase Mcaleese
Chase Mcaleese

Reputation: 28

In plain English, indexed tables can make SELECT faster because it maintains something like an index in a book for the table. Imagine you have to find a specific sentence in a book; isn't it easier if you know what chapter it's in? Or better yet, you can search for a keyword in the index?

So say you have a table with a bunch of names. If you create an index on names, it will sort the table alphabetically by name. A query like "SELECT id FROM users WHERE name='Zach'" will know approximately where that record is located instead of having to look through each record one by one until it finds the correct row.

There can be downsides to indexes though. Indexes have to be updated to remain accurate when rows are inserted and deleted, and that requires computing power. That is why you can't just index every column you add and assume that you're doing a good thing.

Another important note is that the more unique the data in the column is, the more effective the index will be. A column of social security numbers will have a more effective index than a column entitled "political_party" in a table full of congressmen.

Upvotes: 0

ktm5124
ktm5124

Reputation: 12123

Indexes create a separate file where the records are sorted in a binary tree. The lookup for records then becomes a binary search operation, as opposed to a full table scan, making it extremely faster to lookup records by an indexed field.

Primary key lookup is even faster because, unlike indexes, the records are stored along with the key. That is, the record is stored in the leaf of the binary tree. With indexes, only the indexed field is stored in the leaf, and the lookup requires an additional I/O operation to lookup the record.

When you index a field you end up creating a rather large index file, which is why you shouldn't index any fields that don't require indexing.

Upvotes: 5

Related Questions