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…
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.
Please provide following info to better understand and for analysis and to give you suggestions for query tuning.
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