Please tell how to imporve this query in mysql, how to optimise it , how to execute it faster ???

MySQL & MariaDB Other MySQL® Questions
1

[B]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[/B]

please tell what should i do to make this query execute faster…

2

using EXPLAIN get query execution plan, get cardinality and number of rows being scanned to fetch the records. Based on EXPLAIN output create appropriate index on fields in WHERE/ORDER BY clause.

3

Please provide following info to better understand and for analysis and to give you suggestions for query tuning.


EXPLAIN Query\G
SHOW CREATE TABLE trains\G
SHOW CREATE TABLE train_schedule\G
SHOW TABLE STATUS LIKE 'trains'\G
SHOW TABLE STATUS LIKE 'train_schedule'\G
SHOW INDEXES FROM trains\G
SHOW INDEXES FROM train_schedule\G