Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

The Importance of Indexing

DZone's Guide to

The Importance of Indexing

· Integration Zone
Free Resource

Today’s data climate is fast-paced and it’s not slowing down. Here’s why your current integration solution is not enough. Brought to you in partnership with Liaison Technologies.

Problem Statement: In the code we had a JDBC select query on 2 columns of a database table. The query was like this.


SELECT NAME FROM EMPLOYEE WHERE AGE=? AND DEPT=? ;


This query used to take 0.05 seconds to execute.

There was a change request in a release under which we had to modify this query to add another column in the where clause. Now the query looked like.

SELECT NAME FROM EMPLOYEE WHERE AGE=? AND DEPT=? AND COUNTRY=?;


This query now took 0.5 seconds to execute which was 10 times more than the earlier query and surprisingly no new record was added to the table, which was puzzling us as to where the problem is ?

Solution: On checking the indexes on this table, we saw there was an index IDX_EMPLOYEE_AGE_DEPT on columns AGE and DEPT.

So we dropped that index as there was no other query using this index and created a new index IDX_EMPLOYEE_AGE_DEPT_COUNTRY on columns AGE, DEPT and COUNTRY.

CREATE INDEX IDX_EMPLOYEE_AGE_DEPT_COUNTRY ON EMPLOYEE(AGE, DEPT,COUNTRY);


Since, this table had 75 lakhs record so it took considerable time to create this index but after that the same query got executed in 0.06 seconds which was required.

This problem statement and solution helps us in understanding the importance on indexing.

Is iPaaS solving the right problems? Not knowing the fundamental difference between iPaaS and iPaaS+ could cost you down the road. Brought to you in partnership with Liaison Technologies.

Topics:

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}