In my application, my module uses the following tables
- PUBLIC_APPLICATION
- CATEGORY_MASTER
- NOTIFICATION_SITE_DETAIL
- DIMENSION_MASTER DM
- PUBLIC_REGISTRATION
- 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.