Search code examples
mysqlsqldatabaseviewindexing

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?

Solution

  • 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!