如何优化mysql子查询性能? [英] How to optimize mysql subquery performance?

问题描述

亲爱的所有人，

我有这样的子查询

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

推荐答案

您好，Jayaram，

因为您正在使用子查询的聚合函数，所以您的查询可能会继续用于扫描(表/索引).如果表中的记录数很大，那么您的响应时间可能会产生重大影响.

如果您在MySQL中具有TOP函数，则可以将子查询重新编写为从表名WHERE col1 = m.col1按col3 desc选择SELECT top 1 col3"

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"

这篇关于如何优化mysql子查询性能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！