Why multiple joins takes having many rows too long in mysql?

See more:

SQL

MySQL

C#4.0

I have written many stored procedures in this database and almost all queries which containing more than 2 joins are running very slows and takes 15-20 seconds to execute.

Please note I can not edit schema.
I am sharing here one from those procedure as below,
help to find out mistake if any or suggest method to overcome this problem.

What I have tried:

<pre>-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Proc_BarcodeScanDetail`(IN scancode TEXT)
BEGIN


IF ((select Barcode from location_barcode where Barcode = scancode limit 1) = scancode)
THEN

SELECT distinct hg.SKU_code,SKU_NAME,COMPRESSION_FACTOR,REF_BATCH_CODE,EXPIRY_DATETIME,
hg.MRP,SOH/COMPRESSION_FACTOR as 'SOH',BARCODE 
FROM Master sm inner join stock_master hg
ON sm.code = hg.code 
inner join location_barcode lb 
on hg.SKU_LOC_STOCK_NO = lb.sku_Loc_Stock_No
WHERE lb.Barcode = scancode 
and SOH/COMPRESSION_FACTOR >0 limit 1;

ELSEIF ((select EAN_code from ean_sku_link where EAN_code = scancode limit 1)= scancode)
THEN

SELECT hg.SKU_code,SKU_NAME,COMPRESSION_FACTOR,REF_BATCH_CODE,EXPIRY_DATETIME,
hg.MRP,SOH/COMPRESSION_FACTOR as 'SOH',EAN_code 
FROM Master sm inner join stock_master hg
ON sm.code = hg.code 
inner join ean_sku ean 
on hg.SKU_CODE = ean.SKU_CODE
WHERE ean.EAN_code = scancode 
and SOH/COMPRESSION_FACTOR >0 limit 1;
end if;
END

In this procedure; the subqueries in IF and ELSE statement finds that scanned barcode is global(company generated) or local(personally generated). Also if scanned barcode is local then executed 1st main query and if scanned barcode is global then execute 2nd main query. I used inner join for "Master" , "stock_master" ,"location_barcode"/"ean_sku".
It takes 5-8 seconds for executing subqueries and 10-12 seconds for main queries, hence 15-20 seconds for whole procedure.

table_name rows count
location_barcode 378150
master 76573
stock_master 280001
ean_sku 18233