php - Query execution speed when finding SUM
330
In database,i have accounts details table, that contain approx.,300000 records based on the below query I have to show the report in table, account name and selected company value in year-wise, nearly 40 rows and 13 columns
I have the query,
$sql = ("SELECT sum(value) as val FROM balancesheet where formula='$formulaid' and year='$year' and nse_code='$companyCode'");
based on the year, company, and formula, here formula field, suppose if formula is 1+2+3 means, the query will execute 3 times inside the loop and continue again with parent loop
o/p: will be like
2012 2011 2000.........
account name1 15.00 16.08 47.55
account name2 15.00 16.08 47.55
.......
so hardly it takes 4 to 5 mins to print. Kindly help me how to improve the execution of query to print faster
Answer
Solution:
If these columns are always searched together, define an index on them,
Answer
Solution:
Use EXPLAIN to find out what is happening under the bonnet (hood for the Yanks). Then you are in a position to create the appropriate indexes.
The indexes can be created via the table definition or via create index. It is also useful to look into optimize
Answer
Solution:
Add indexes on where fields to improve speed.