Sixth Chapter Lesson-6: Database sorting and indexing.

At the end of this lesson-

• 1. You will be able to explain concept of database sorting.
• 2. You will be able to explain concept of database indexing.
• 3. You will be able to describe advantages and disadvantages of indexing.
• 4. You will be able to differentiate between sorting and indexing.

Database Sorting: Sorting is the process of arranging the records in either ascending or descending order based on specific fields to make reported data more usable.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.

Database sorting are two types-

• 1.Ascending Order
• 2.Descending Order

The records of the following student_info table are sorted based on GPA field in descending order-

Purpose of database sorting:

• Keeping a table or index sorted can speed up range queries. For example, keeping a table of temperature measurements sorted by timestamp make retrieving all measurements for a given day or week more efficient, because it minimizes the number of disk pages to read.
• Sorting can improve data compression. For example, sorting a group of rows can create long runs of the same value, which will make run-length encoding of columns more beneficial.

Syntax of SQL Command for selecting data from a table and showing in ascending order: 

SELECT */ column_name_1, column_name_2, .. FROM table_name
ORDER BY column_name ASC;

SQL Command for showing data from the above “student_info” table in ascending order- 

SELECT * FROM student_info ORDER BY Id ASC;

After running the SQL command, the output table will be like following table-

Syntax of SQL Command for showing data from a table in descending order: 

SELECT */ column_name_1, column_name_2, .. FROM table_name
ORDER BY column_name DESC;

SQL Command for showing data from the above “student_info” table in descending order- 

SELECT * FROM student_info ORDER BY Id DESC;

After running the SQL command, the output table will be like following table-

Database Indexing: Indexing is a method that is used to improve the data retrieval speed in a table of a database. Indexing in database systems is similar to what we see in books. 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. An index file helps to quickly locate and access the data in a database 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. The users cannot see the indexes, they are just used to speed up searches/queries.

Some important points should be kept in minds when creating an index-

• 1. Normally index should be created based on key field of a table.
• 2. Index’s name should be given similar to field’s name that is used to make index. As a result, it will be easy to remember Index’s name.
• 3. A database table may have one or more index and they can be opened at the same time but only one index will be active at a time.

The advantages of indexes are as follows:

• 1. Their use in queries usually results in much better performance.
• 2. They make it possible to quickly retrieve (fetch) data.
• 3. Index file is updated automatically if any record is inserted into the table.
• 4. Unique indexes guarantee uniquely identifiable records in the database.

The disadvantages of indexes are as follows:

• 1. They decrease performance on inserts, updates, and deletes.
• 2. They take up space (this increases with the number of fields used and the length of the fields).

Difference between sorting and indexing:

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.

Syntax of SQL command for creating INDEX:

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name
ON table_name (column1, column2, ...);

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

DROP INDEX index_name ON table_name;

Lesson Evaluation-

Knowledge Based Questions:

• a. What is database sorting?
• a. What is database indexing?

Comprehension Based Questions:

• b. “Database sorting and Indexing are not same” – Explain.
• b. “It is not possible to do sorting on OLE Object”- Explain.
• b. On which data types sorting is not possible? Explain.
• b. If data is inserted in the indexed table, index is updated automatically. -Explain.
• b. Indexing makes database faster for functioning – Explain.
• b. Indexing does not change the main file of the database. -Explain.
• b. Why does Sorting need more memory than Indexing? Explain.

Creative Questions:

Multiple Choice Questions:

Written by,

• Mizanur Rahman (Mizan)
• Lecturer of ICT, Shaheed Bir Uttam Lt. Anwar Girls’ College ,Dhaka Cantonment
• Author at www.edupointbd.com
• Software Engineer at mands IT
• Former Lecturer of ICT, Cambrian College, Dhaka
• Email: mizanjust@gmail.com
• Cell: 01724351470