Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
How to optimize mysql subquery performance? RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    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

    • Moved by Nai-dong Jin - MSFT Friday, November 27, 2009 5:49 AM Unsupported tech. (From:SQL Server Data Access)
    Monday, November 23, 2009 6:52 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi Jayaram,

    Because you are using a aggregate function your subquery, your query may go for scan(table/index). If the no.of records in the table is huge then your response time may have significant impact.

    If you have TOP function in MySQL then you can re-write your sub query as "SELECT top 1 col3 FROM tablename WHERE col1=m.col1 order by col3 desc"
    Regards,Balaji Please mark as answer if you think this answers your questions
    Monday, November 23, 2009 9:07 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi Balaji,
     
        
           Thank you for the response. MySql didnot have TOP function.
    Tuesday, November 24, 2009 6:34 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi

    You can use limit keyword instead of top key word in Sql Server
    Refer the following website

    http://dev.mysql.com/doc/refman/5.0/en/select.html

    Tuesday, November 24, 2009 10:08 AM