Indexing is a method that is used to improve the data retrieval speed in a table of a database. An index could be created using a single or more columns in a table and the index is stored in a separate file. Indices can be created as unique indices or non-unique indices. Sorting is the process or arranging items in a set in a specific order. Sorting a table would create a copy of the table in which the rows may have a different order than the original.
What is Indexing?
Indexing is a method that is used to improve the data retrieval speed in a table of a database. An index could be created using a single or more columns in a table and the index is stored in a separate file. This file contains the logical order of rows along with their physical position in the table. The space required by an index file is typically less than the space required to store the table. Unique indices will prevent the table from containing duplicate values of the index. Indexing would make the data retrieval more efficient. Consider the following SQL statement.
SELECT first_name, last_name FROM people WHERE city = ‘New York’
If the above query was executed in a table that does not have an index created using the city column, it has to scan the whole table and look at the city column of each row to find all the entries withcity=”New York”. But if the table had an index, it will simply follow using a B-tree data structure until the entries with the “New York” is found. This would make the search more efficient.
What is Sorting?
Sorting is the process or arranging items in a set in a specific order. Sorting a table would create a copy of the table in which the rows may have a different order than the original. Storing the new table would require an amount of space similar to that of the original table. Due to this reason sorting is used less frequently; only used when a new copy of the sorted table is required. Sorting is allowed using multiple fields, such as sorting addresses using the states and then sort using the cities inside the states.
What is the difference between Indexing and Sorting?
Indexing and sorting are two methods that can be used to create an order in a data table. Indexing would create an index file that contains only the logical order of rows along with their physical position in the table whereas with sorting, a copy of the sorted table has to be stored. Usually, the index file requires lesser space than storing a sorted table. Furthermore, some operations like running queries and searching would be faster with a table with indexes. In addition, indexing would not change the original order in the table, while sorting would change the order of rows. Also, operation such as linking tables would require having an index.