How to optimize mysql subquery performance?

• • Reply

  11 years ago

  by Jayaram P

  Jayaram P

  Visakhapatnam, India

  Joined 11 years ago

  Dear All,

   I have subquery like this
  

  SELECT col1,col2,col3,col4,col5 FROM tablename as m WHERE col3 =(SELECT MAX(col3)FROM tablename WHERE col1 = m.col1)ORDER BY col1

  here col3 as date column

  Here my table contains around 7000000 records

  But it was taking too much time to execute this subquery. And also we have created index for col1 and col3. And we have used dataset and datareader for load the data. But the query was taking more time to execute. And we have tried with limit command also but there is no use.

  How to increase the execution performance of the subquery?. I need to fill all the results into dataset or datareader with in seconds (with less time).

  We are using C# with MYSQL database.

  Any help must be appreciated.

  Thanks & Regords Jayaram

  Report abuse