MySQL Forums :: Performance :: To store or calculate on the spot?


Advanced Search

To store or calculate on the spot?
Posted by: newbie Shai ()
Date: July 23, 2011 09:15AM

Dear All,
I got few tables as below. So then what I do on any time I will calculate the total points collected based on each table via some of this queries like below. What I am using now is the sum function. I am confuse should I like run a monthly service which will total and store in separate table the total for each month rather than doing it live calculation? Which one is more effective?

Query 1
Select sum(tblDriverF2FPrograms.f2FProgramsPoints) As sumF2FPrograms FROM tblDriverF2FPrograms Where tblDriverF2FPrograms.driverID=".$driverID. " And tblDriverF2FPrograms.clientID=".$clientID." And Month(tblDriverF2FPrograms.dateTimeInsert)=".$monthValue. " And Year(tblDriverF2FPrograms.dateTimeInsert)=".$yearValue

Query 2
Select sum(tblDriverMerits.meritsPoints) As sumMerits FROM tblDriverMerits Where tblDriverMerits.driverID=".$driverID. " And tblDriverMerits.clientID=".$clientID." And Month(tblDriverMerits.dateTimeInsert)=".$monthValue. " And Year(tblDriverMerits.dateTimeInsert)=".$yearValue

Table 1
CREATE TABLE IF NOT EXISTS `tblDriverF2FPrograms` (
`driverF2FProgramsID` int(5) NOT NULL auto_increment,
`clientF2FProgramsID` int(5) NOT NULL,
`driverID` int(5) NOT NULL,
`clientID` int(5) NOT NULL,
`employeeIDInsert` int(5) NOT NULL,
`dateTimeInsert` datetime NOT NULL,
`employeeIDVerify` int(5) NOT NULL,
`dateTimeVerify` datetime NOT NULL,
`f2FProgramsPoints` int(2) NOT NULL,
`driverF2FProgramsUpdateStatus` enum('Draft','Approved') NOT NULL,
PRIMARY KEY (`driverF2FProgramsID`),
KEY `driverID` (`driverID`),
KEY `clientID` (`clientID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


Table 2
CREATE TABLE IF NOT EXISTS `tblDriverMerits` (
`driverMeritsID` int(5) NOT NULL auto_increment,
`clientMeritsID` int(5) NOT NULL,
`driverID` int(5) NOT NULL,
`clientID` int(5) NOT NULL,
`employeeIDInsert` int(5) NOT NULL,
`dateTimeInsert` datetime NOT NULL,
`employeeIDVerify` int(5) NOT NULL,
`dateTimeVerify` datetime NOT NULL,
`meritsPoints` int(2) NOT NULL,
`driverMeritsUpdateStatus` enum('Draft','Approved') NOT NULL,
PRIMARY KEY (`driverMeritsID`),
KEY `driverID` (`driverID`),
KEY `clientID` (`clientID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Options: ReplyQuote


Subject Views Written By Posted
To store or calculate on the spot? 2386 newbie Shai 07/23/2011 09:15AM
Re: To store or calculate on the spot? 960 John Nahlen 07/23/2011 11:31AM
Re: To store or calculate on the spot? 1008 Rick James 07/23/2011 02:46PM
Re: To store or calculate on the spot? 864 newbie Shai 07/23/2011 07:40PM
Re: To store or calculate on the spot? 924 John Nahlen 07/23/2011 10:08PM
Re: To store or calculate on the spot? 897 newbie Shai 07/23/2011 10:34PM
Re: To store or calculate on the spot? 891 Rick James 07/23/2011 10:18PM
Re: To store or calculate on the spot? 773 newbie Shai 07/24/2011 07:39PM
Re: To store or calculate on the spot? 987 Rick James 07/24/2011 09:14PM
Re: To store or calculate on the spot? 905 newbie Shai 07/27/2011 01:00AM
Re: To store or calculate on the spot? 1061 Rick James 07/27/2011 11:12PM
Re: To store or calculate on the spot? 807 newbie Shai 07/28/2011 12:09PM
Re: To store or calculate on the spot? 849 Rick James 07/28/2011 07:11PM
Re: To store or calculate on the spot? 712 newbie Shai 07/31/2011 04:03AM
Re: To store or calculate on the spot? 809 Rick James 08/10/2011 07:22PM
Re: To store or calculate on the spot? 788 newbie Shai 08/11/2011 02:21PM
Re: To store or calculate on the spot? 858 Rick James 08/11/2011 07:52PM
Re: To store or calculate on the spot? 814 newbie Shai 08/11/2011 08:07PM
Re: To store or calculate on the spot? 730 Rick James 08/12/2011 07:21PM
Re: To store or calculate on the spot? 772 newbie Shai 08/12/2011 07:26PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.