my mysql query optimization,execute query fast

[Kamlesh_Rathore]

SELECT train_no,train_name
FROM trains
WHERE train_no
IN (

SELECT s1.train_no
FROM train_schedule AS s1
INNER JOIN train_schedule AS s2
WHERE s1.stn_code = 'JU'
AND s2.stn_code = 'JP'
AND s1.distance < s2.distance
AND s1.train_no = s2.train_no
) ORDER BY train_no LIMIT 0 , 30


this query takes 50 seconds to executes on my server
i want quick response

please help what kind of changes i should done

Edited August 19, 2013 by Kamlesh_Rathore

[Barand]

Does this help

SELECT t.train_no, t.train_name
FROM trains t
    INNER JOIN train_schedule AS s1 
        ON t.train_no = s1.train_no AND s1.stn_code = 'JU'
    INNER JOIN train_schedule AS s2
        ON t.train_no = s2.train_no AND s2.stn_code = 'JP'
WHERE s1.distance < s2.distance
ORDER BY train_no 
LIMIT 0 , 30

If not, can you post a test dump of the two  tables