查询优化 [英] Query optimisation

问题描述

在我的申请中，我的单元使用以下表格

In my application, my module uses the following tables

1. PUBLIC_APPLICATION


2. CATEGORY_MASTER
3. 
   
4. 
   
5. 
   
6. DIMENSION_MASTER DM


7. PUBLIC_REGISTRATION


8. ALLOTMENT_NOTIFICATION。 li>
   

1. PUBLIC_APPLICATION
2. CATEGORY_MASTER
3. NOTIFICATION_SITE_DETAIL
4. DIMENSION_MASTER DM
5. PUBLIC_REGISTRATION
6. ALLOTMENT_NOTIFICATION.

从以下表中检索数据

SELECT PA.REGISTRATION_NO,PA.APP_ID,PA.NO_OF_ATTEMPTS,CM.CATEGORY_NAME,
DM.SITE_DIMENSION,PR.BDA_NO,AN.NOTIFY_ID 
 FROM **PUBLIC_APPLICATION PA,CATEGORY_MASTER CM,NOTIFICATION_SITE_DETAIL NSD,DIMENSION_MASTER DM, PUBLIC_REGISTRATION PR,ALLOTMENT_NOTIFICATION AN** 
WHERE **CM.CATEGORY_ID = PA.CATEGORY_ID AND 
NSD.NOTIFY_SITE_ID = PR.NOTIFY_SITE_ID AND 
DM.DIMENSION_ID = NSD.DIMENSION_ID AND 
PR.REGISTRATION_NO = PA.REGISTRATION_NO AND 
AN.NOTIFICATION_NO = PA.NOTIFICATION_NO AND
PR.NOTIFY_SITE_ID = PA.NOTIFY_SITE_ID AND NSD.NOTIFY_ID = AN.NOTIFY_ID AND 
PA.NOTIFICATION_NO = ?**  LIMIT ?, ?

PUBLIC_APPLICATION& PUBLIC_REGISTRATION有大量的数据，接近10万条记录和其他表有大约5000条记录。

PUBLIC_APPLICATION & PUBLIC_REGISTRATION have large number of data, nearly i Million records and other tables have around 5000 records.

如果我执行上面的查询，需要30多分钟才能得到结果，任何一个建议我写效率查询在最短的时间内获得结果。

If I execute the above the query it takes more than 30 min to get the results, Can any one suggest me to write the effiecient query to get the results within minimum time.

忘记指定，我使用mysql数据库。 p>

forgot to specify, I am using the mysql database for this.

推荐答案

通过使用STRAIGHT_JOIN，您可以告诉优化器这样做。
我将通知＃作为第一个WHERE子句，所以它被首先处理限制你的设置。 THEN，我将连接设置为其他表。我有这个之前，当查询15万条记录的gov't数据加入15+表，它花了20多个小时。只要在我已经格式良好的查询中加入「STRAIGHT_JOIN」，大约需要2小时...再过1500万笔纪录，并加入超过15个表格以描述儿童的详细资料。

By using "STRAIGHT_JOIN", you tell the optimizer to do it as you say so. I moved the Notification # as the first WHERE clause so it gets handled first to limit your set. THEN, I set the joins to the other tables. I had this before when querying gov't data of 15+ million records to join 15+ tables and it took 20+ hours. By adding just the "STRAIGHT_JOIN" to my already well-formed query, it took about 2 hours ... again, 15+ million records and join to over 15 tables for child descriptive details.

SELECT STRAIGHT_JOIN
        PA.REGISTRATION_NO,
        PA.APP_ID,
        PA.NO_OF_ATTEMPTS,
        CM.CATEGORY_NAME, 
        DM.SITE_DIMENSION,
        PR.BDA_NO,
        AN.NOTIFY_ID 
    FROM 
        PUBLIC_APPLICATION PA,
        CATEGORY_MASTER CM,
        NOTIFICATION_SITE_DETAIL NSD,
        DIMENSION_MASTER DM, 
        PUBLIC_REGISTRATION PR,
        ALLOTMENT_NOTIFICATION AN 
    WHERE 
            PA.NOTIFICATION_NO = ? 
        AND PA.CATEGORY_ID = CM.CATEGORY_ID
        AND PA.REGISTRATION_NO = PR.REGISTRATION_NO
        AND PA.NOTIFICATION_NO = AN.NOTIFICATION_NO
        AND PA.NOTIFY_SITE_ID = PR.NOTIFY_SITE_ID 
        AND PR.NOTIFY_SITE_ID = NSD.NOTIFY_SITE_ID
        AND NSD.DIMENSION_ID  = DM.DIMENSION_ID
        AND NSD.NOTIFY_ID = AN.NOTIFY_ID 
    LIMIT 
        ?, ?

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