0

How to understand whether a table performance is increased after adding index? I have a table field with 150 000 data. I have created another table field_test same as field. I have added index in field_test. Field does not have index. How to understand is there any change in performance.

Structure of Field and Field_test both are as follows:

  • Field_id int primary key
  • Field_name varchar
  • DateOfCreation Datetime

I have added clustered index on the Field_id column.

James Z
  • 12,209
  • 10
  • 24
  • 44
Anita Prasad
  • 25
  • 6
  • you can read `@Xenph Yan` 's answer [sql - How does database indexing work? - Stack Overflow](https://stackoverflow.com/questions/1108/how-does-database-indexing-work?rq=1) . – Wei Lin Jul 08 '18 at 06:17
  • The usefulness of an index will depend on the queries. Execute the query from an SSMS query window with the menu option `Query-->Include Actual Execution Plan` selected to view the graphical plan and index use (or not). – Dan Guzman Jul 08 '18 at 12:04

1 Answers1

0

Firstly, a clustered index rearranges the physical location of the data(rows) in the hard disk allowing for a faster search.

To test this you can write a query similar to select * from field_test where field_id=xxx

If you run this query on the table without an index, you will find it to be a tad bit slower than running it on an indexed table. We would still want to quantify the difference an index makes.

To do that, you can use an EXPLAIN query. An Explain query would look like explain select * from field_test where field_id=xxx This will give you an approximation of the number of rows that are actually looked at to arrive at the resultant set of rows.

Running the explain query on an indexed table will usually give you a significantly smaller number as compared to on nonindexed tables.

Rocode
  • 108
  • 6