What is difference between INDEX and VIEW in MySQL

Question

Which one is fast either Index or View both are used for optimization purpose both are implement on table's column so any one explain which one is more faster and what is difference between both of them and which scenario we use view and index.

Solution 1

VIEW

• View is a logical table. It is a physical object which stores data logically. View just refers to data that is tored in base tables.
• A view is a logical entity. It is a 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 pointres that maps to the physical address of 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 in a shop, assume you have multiple racks. Categorizing each rack based on the items saved is like creating an index. So, you would know where exactly to look for to find a particular item. This is indexing.

In the same shop, you want to know multiple data, say, the Products, inventory, Sales data and stuff as a consolidated report, then it can be compared to a view.

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

Solution 2

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.

Solution 3

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.