Query optimisation

Question

In my application, my module uses the following tables

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

From the following tables I am retrieving the data

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 have large number of data, nearly i Million records and other tables have around 5000 records.

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.

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

Answer 1

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 
        ?, ?