SQL Server - how indexes make search faster - Asked By aman on 13-Jul-11 05:37 AM

hi all,

i want to do practical demonstration to see how exactly the indexes make search faster ....using set statistics io on
please give me some example so that i can make use of it.

Thanks and Regards,
Aman Khan
Ravi S replied to aman on 13-Jul-11 05:39 AM

Indexing is one of the more useful features of MySQL. MySQL allows several types of indexes like http://www.mysqlfaqs.net/mysql-faqs/Indexes/What-is-primary-key-index-in-MySQL, http://www.mysqlfaqs.net/mysql-faqs/Indexes/What-is-unique-key-or-index-in-MySQL, http://www.mysqlfaqs.net/mysql-faqs/Indexes/What-is-index-or-key-without-constraints-in-MySQL also known as ("non-unique index", ordinary index, index without constraints") and http://www.mysqlfaqs.net/mysql-faqs/Indexes/What-is-full-text-index-in-MySQL. Of course, the indexes improve SELECT queries speed tremendously. but, they do have some considerable disadvantages as well.

Advantages of MySQL Indexes

Generally speaking, MySQL indexing into database gives you three advantages:

  • Query optimization: Indexes make search queries much faster.
  • Uniqueness: Indexes like primary key index and unique index help to avoid duplicate row data.
  • Text searching: Full-text indexes in MySQL version 3.23.23, users have the opportunity to optimize searching against even large amounts of text located in any field indexed as such.

Disadvantages of MySQL indexes

When an index is created on the column(s), MySQL also creates a separate file that is sorted, and contains only the field(s) you're interested in sorting on.

Firstly, the indexes take up disk space. Usually the space usage isn’t significant, but because of creating index on every column in every possible combination, the index file would grow much more quickly than the data file. In the case when a table is of large table size, the index file could reach the operating system’s maximum file size.

Secondly, the indexes slow down the speed of writing queries, such as INSERT, UPDATE and DELETE. Because MySQL has to internally maintain the “pointers” to the inserted rows in the actual data file, so there is a performance price to pay in case of above said writing queries because every time a record is changed, the indexes must be updated. However, you may be able to write your queries in such a way that do not cause the very noticeable performance degradation.

refer the links also



Ravi S replied to aman on 13-Jul-11 05:40 AM

You can do it by first creating a tablespace with 32k blocksize and then create index under it,

create tablespace index_ts
datafile ‘/u01/index_file.dbf’ size 1024m
blocksize 32k;

create index emp_idx on emp(id,code) parallel 5 nologging  tablespace index_ts;

refer the link for details


Reena Jain replied to aman on 13-Jul-11 05:40 AM

Indexes are created on columns in tables or views. The index provides a fast way to look up data based on the values within those columns. For example, if you create an index on the primary key and then search for a row of data based on one of the primary key values, SQL Server first finds that value in the index, and then uses the index to quickly locate the entire row of data. Without the index, a table scan would have to be performed in order to locate the row, which can have a significant effect on performance.

You can create indexes on most columns in a table or a view. The exceptions are primarily those columns configured with large object (LOB) data types, such as image, text, and varchar(max). You can also create indexes on XML columns, but those indexes are slightly different from the basic index and are beyond the scope of this article. Instead, I'll focus on those indexes that are implemented most commonly in a SQL Server database.

Hope this will help you

Jitendra Faye replied to aman on 13-Jul-11 05:41 AM

Advantages of an index over no index

If no index exists on a table, a table scan must be performed for each table referenced in a database query. The larger the table, the longer a table scan takes because a table scan requires each table row to be accessed sequentially. Although a table scan might be more efficient for a complex query that requires most of the rows in a table, for a query that returns only some table rows an index scan can access table rows more efficiently.

The optimizer chooses an index scan if the index columns are referenced in the SELECT statement and if the optimizer estimates that an index scan will be faster than a table scan. Index files generally are smaller and require less time to read than an entire table, particularly as tables grow larger. In addition, the entire index may not need to be scanned. The predicates that are applied to the index reduce the number of rows to be read from the data pages.

If an ordering requirement on the output can be 7 matched with an index column, then scanning the index in column order will 7 allow the rows to be retrieved in the correct order without a sort.

Each index entry contains a search-key value and a pointer to the row containing that value. If you specify the ALLOW REVERSE SCANS parameter in the CREATE INDEX statement, the values can be searched in both ascending and descending order. It is therefore possible to bracket the search, given the right predicate. An index can also be used to obtain rows in an ordered sequence, eliminating the need for the database manager to sort the rows after they are read from the table.

In addition to the search-key value and row pointer, an index can contain include columns, which are non-indexed columns in the indexed row. Such columns might make it possible for the optimizer to get required information only from the index, without accessing the table itself.

The existence of an index on the table being queried does not guarantee an ordered result set. Only an ORDER BY clause ensures the order of a result set.

Follow this link also-


Hope this will help you.