Not the answer you need?
Register and ask your own question!

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

masterkamleshmasterkamlesh EntrantInactive User Role Beginner
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




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

Comments

  • yogesh777yogesh777 Contributor Current User Role Beginner
    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.
  • mirfanmirfan Database Administrator Inactive User Role Beginner
    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
    
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.