I have this query which is when run against 100K rows, takes forever to result in my system with 2 GB Ram and standard MySQL installation.
Now in my database , I have two tables, chitentry and chitentrygroup. Both contain more then 100K rows. When I run the following query the result is always pending and I do not get any output and the query keeps running.
SELECT SQL_CALC_FOUND_ROWS chitentrygroupid, chitentrygroupdate, chitentryno, chitbookno, chitamount, entryamount, entryweight, employeename, accountname, rate, monthentry FROM (SELECT chitentrygroup.chitentrygroupid, chitentrygroup.chitentrygroupdate, chitentry.chitentryno, chitentry.chitbookno, Round(( chitentry.chitamount ), 0) AS chitamount, chitentry.entryamount, chitentry.entryweight, employee.employeename, account.accountname, chitentry.rate, chitentry.monthentry FROM chitentrygroup LEFT OUTER JOIN chitentry ON chitentry.chitentrygroupid = chitentrygroup.chitentrygroupid LEFT OUTER JOIN employee ON employee.employeeid = chitentrygroup.employeeid LEFT OUTER JOIN account ON account.accountid = chitentrygroup.accountid WHERE chitentrygroup.status NOT IN ( 0, 3, 8 ) AND chitentrygroup.industryid IN ( 3 ) AND 1 = 1 AND 1 = 1 AND 1 = 1 GROUP BY 1 HAVING 1 = 1 ORDER BY chitentrygroup.chitentrygroupid DESC ) AS t LIMIT 0, 10
Now when I remove SQL_CALC_FOUND_ROWS , Group By and Having Part of the query. The query is blazing fast. The presence of any of the above I stated makes the query stuck with this big table.
How should I optimize this query with Group By,Having and SQL_CALC_FOUND_ROWS Clause?