How INDEX helped him get out of the trouble with time complexity!!!

Have you ever gone into a practical situation where you thought that the concept of INDEX could help getting out of a real time problem???

I guess I will get a majority of “NO”.

Here is a real time demonstration of how index can come as the only help improving the issue of time complexity in fetching records from a table. In his case the number of rows fetched reduced from 17443 to 81 in a JOIN query when used with index applied. Check below…

Problem Specification

I have two tables “Category” table having one of columns as “category name” and “Professional” tables, having one of the columns as “category id“.
Now I am searching for certain categories in “Category” table, then getting those professionals that have that category.
The problem that I am facing is that in case of joining “Professional” and “category” table its looking as if too many rows in “Professional” table are being searched. (Ignore if I am wrong)

Is it possible that join only happens on those rows that have been returned from search in “Category” table.

enter image description here

explain select c.id from professional_categories as c  
inner join professionals as p  on p.professional_category_id=c.id 
where c.professional_category_name like 'ABC%'

Below are the indices from professional table. enter image description here

Check this link for the solution and comments on this.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s