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

720

Answer

Solution:

If these columns are always searched together, define an index on them,

ALTER TABLE balancesheet ADD INDEX (formula, year, nse_code)
410

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

789

Answer

Solution:

Add indexes on where fields to improve speed.

People are also looking for solutions to the problem: JSON using PHP to get selected values

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.