How to optimize mysql subquery performance?

DataReader , Csharp , MySql , SubQuery , DataSet India
  • 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

Post a reply

No one has replied yet! Why not be the first?

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“A computer lets you make more mistakes faster than any other invention in human history, with the possible exceptions of handguns and tequila” - Mitch Ratcliffe