Using a Primary Key vs Table Column In Query Reduces Query Time Why?

[Failing_Solutions]

This question is only for my self education. Even if there are no good answers I hope my findings help others.

 

I wrote a query that was designed to find all parts in a particular location, the webpage simply requested the user to give the location then the query would find all parts in that location it looked like this.

SELECT p.part_number,t.transaction_id, t.received_id, t.quantity, l.location, g.group_id
              FROM transactions AS t
              JOIN operators AS o ON o.operator_id = t.operator_id
              JOIN parts AS p ON p.part_id = t.part_id
              JOIN actions AS a ON a.action_id = t.action_id
              JOIN locations AS l ON l.location_id = t.location_id
              JOIN received AS r ON r.received_id = t.received_id
              JOIN group_family AS g ON g.received_id = r.received_id
              WHERE l.location= '$loc'
              AND  t.action_id IN('1','3','5','8','10','12','14','15','17')
              AND l.consumption='Storage'
              AND t.quantity > 0
              ORDER BY p.part_number, r.received_id

This query works, but took a very long time to execute, an annoying long time if the location had many parts / records.

 

 

The time it took for this query to execute seemed way off to me so I started tinkering. I decided to change the WHERE l.location to where l.location_id = so it looked like this

SELECT p.part_number,t.transaction_id, t.received_id, t.quantity, l.location, g.group_id
              FROM transactions AS t
              JOIN operators AS o ON o.operator_id = t.operator_id
              JOIN parts AS p ON p.part_id = t.part_id
              JOIN actions AS a ON a.action_id = t.action_id
              JOIN locations AS l ON l.location_id = t.location_id
              JOIN received AS r ON r.received_id = t.received_id
              JOIN group_family AS g ON g.received_id = r.received_id
              WHERE l.location_id= '$loc_id'
              AND  t.action_id IN('1','3','5','8','10','12','14','15','17')
              AND l.consumption='Storage'
              AND t.quantity > 0
              ORDER BY p.part_number, r.received_id

And I was simply amazed in the difference, example; 1st query could take 50 seconds, second query only 2 seconds.

 

The only difference is that instead of using the locations table "location" column, I am using the locations table location_id column which is the primary key.  I'm wondering why this saves so much execution time?

 

 

I had always been under the impression that you should try to limit the webpage to as few queries as possible, however using this method I have to take the users inputted location, and query the location_id. In the end, I am running 2 queries one to get the location id, then one to get the results, and yet I'm this is 1000% faster then the original query. 

 

Any thoughts, or input is appreciated.

 

Thank you

[Barand]

Probably you don't have an index on location name so is has to read and compare all rows