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 from professional_categories as c  
inner join professionals as p  on 
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.


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s