CODEWITHSUNDEEP
mysqlsqldatabaseviewindexing
Ashutosh SIngh
Ashutosh SIngh

Reputation: 1019

What is difference between INDEX and VIEW in MySQL

Which one is fast? An Index or a View? Both are used for optimization purposes. Both are implemented against table columns. So can anyone explain which one is faster and what are the differences between both of them and in which scenario do we use a View and an Index?

Upvotes: 13

Views: 56378

Answers (4)

ngrashia
ngrashia

Reputation: 9904

VIEW

  • A View is a logical Table. A Table is a physical object which stores data. Views just refer to data stored in tables.
  • A view is a logical entity. It is an SQL statement stored in the database in the system tablespace. Data for a view is built in a table created by the database engine in the TEMP tablespace.

INDEX

  • Indexes are pointers that map to the physical address of the data. So by using indexes data manipulation becomes faster.
  • An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns.

ANALOGY:

Suppose you're managing a shop. Assume you have multiple racks. Categorizing each rack based on the type of items is like creating an index. So you know where to look to find a particular item. This is indexing.

In the same shop, you want to manage multiple data types, say, the Products, Inventory, Sales and other things such as a consolidated report. Such a report can be compared to a view.

Hope this analogy properly describes when you have to use a view and when you have to use an index!

Upvotes: 24

Z0R0
Z0R0

Reputation: 1

View:

  • A virtual table created by a query.
  • Does not store data; instead, it pulls data from existing tables whenever accessed.
  • Used for simplifying complex queries, enhancing security, and providing a specific data perspective.
  • Can be queried like a regular table but cannot always be updated.

Index:

  • A data structure that improves the speed of data retrieval.
  • Stores pointers to data in tables, helping speed up queries that involve searches or sorting.
  • Used to improve performance but can slow down write operations (like INSERT, UPDATE).

Upvotes: 0

Mahesh Patil
Mahesh Patil

Reputation: 1551

view:

1) view is also a one of the database object. view contains logical data of a base table.where base table has actual data(physical data).another way we can say view is like a window through which data from table can be viewed or changed.

2) It is just simply a stored SQL statement with an object name. It can be used in any SELECT statement like a table.

index:

1) indexes will be created on columns.by using indexes the fetching of rows will be done quickly.

2) It is a way of cataloging the table-info based on 1 or more columns. One table may contain one/more indexes. Indexes are like a 2-D structure having ROWID & indexed-column (ordered). When a table-data is retrieved based on this column (col. which are used in WHERE clause), this index gets into the picture automatically and it's pointer search the required ROWIDs. These ROWIDs are now matched with actual table's ROWID and the records from table are shown.

Upvotes: 1

Ankit Bajpai
Ankit Bajpai

Reputation: 13519

Both are different things in the perspective of SQL.

VIEWS

A view is nothing more than a SQL statement that is stored in the database with an associated name. A view is actually a composition of a table in the form of a predefined SQL query. Views, which are kind of virtual tables, allow users to do the following:

  • A view can contain all rows of a table or select rows from a table. A view can be created from one or many tables which depends on the written SQL query to create a view.
  • Structure data in a way that users or classes of users find natural or intuitive.
  • Restrict access to the data such that a user can see and (sometimes) modify exactly what they need and no more.
  • Summarize data from various tables which can be used to generate reports.

INDEXES

While Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.

For example, if you want to reference all pages in a book that discuss a certain topic, you first refer to the index, which lists all topics alphabetically and are then referred to one or more specific page numbers.

An index helps speed up SELECT queries and WHERE clauses, but it slows down data input, with UPDATE and INSERT statements. Indexes can be created or dropped with no effect on the data.

Upvotes: 5

Related Questions