Announcement

Announcement Module
Collapse
No announcement yet.

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

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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..
    Last edited by masterkamlesh; 08-19-2013, 05:26 AM.

  • #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.

    Comment


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

      Code:
      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

      Comment

      Working...
      X