getting the 3 queries as optimised single query.. is this possible

[hbmarar]

hi experts,

Is it possible that i could get the 3 queries made into one single query.Any help could make me understand and correct myelf in better queries. Each query is taking long time and sometimes the first query crashes , with

> show processlist

showing status as 'copying to tmp data'

Thanks for concern and consideration

Thanks.

Saff

CREATE TABLE `yk_contributor_aging` (
`contributor_id` int(10) NOT NULL default '0',
`descale` float default NULL,
`rating_credit` float NOT NULL default '0',
`comment_credit` float NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

---------------------------------------------------------------------------------------------------------
Query below inserts the contributor id and descale columns ( takes too much time that hangs )
---------

insert into `yk_contributor_aging` ( contributor_id, descale) SELECT yk_contribution.contributor_id, SUM(log(7, 365)-log(7, DATEDIFF(now(), yk_contribution.contribution_date ))) as descale FROM yk_contribution,yk_rating, yk_entity_score WHERE yk_entity_score.entity_key = yk_contribution.key AND DATEDIFF(now(), yk_contribution.contribution_date ) <= 365 GROUP BY yk_contribution.contributor_id ORDER BY yk_contribution.contributor_id ;

----------------------------------------------------------------------------------------------------------
Query below selects comment credit and is written to sql file for inserts the comment_credit column in yk_contributor_aging table

select contribution_date, cor.contributor_id, ( log(10,SUM(((log(7, 365) - log(7,DATEDIFF(NOW(), contribution_date )))/(log(7,365))))) / 3) as credit from yk_contribution as cion,yk_contributor as cor WHERE cor.contributor_id=cion.contributor_id GROUP by cion.contributor_id ORDER BY cion.contributor_id;

-----------------------------------------------------------------------------------------------------------
Query below selects comment credit and is written to sql file for inserts the comment_credit column in yk_contributor_aging table

select rating.contributor_id, ( log(10,SUM(((log(7, 365) - log(7,DATEDIFF(NOW(), rating_date )))/(log(7,365))))) / 4) as credit from yk_rating as rating, yk_contributor as cor WHERE cor.contributor_id = rating.contributor_id GROUP BY contributor_id ORDER BY contributor_id;

[varmadba]

Rather than Joining all 3 into 1 and complicating things u can add some index to tables and optimize select queries to executing will be faster

INSERT INTO `yk_contributor_aging` ( contributor_id, descale) SELECT yk_contribution.contributor_id, SUM(log(7, 365)-log(7, DATEDIFF(now(), yk_contribution.contribution_date ))) AS descale FROM yk_contribution,yk_rating, yk_entity_score WHERE yk_entity_score.entity_key = yk_contribution.KEY AND DATEDIFF(now(), yk_contribution.contribution_date ) <= 365 GROUP BY yk_contribution.contributor_id ORDER BY yk_contribution.contributor_id ;

DATEDIFF(now(), yk_contribution.contribution_date ) <= 365

Above part of query should be changed,even if you have a index on
yk_contribution.contribution_date database will not use,it will simply by pass index and will do a full table scan
its better to pass the date/time value to it

Add some indexes and optimize selects so executing will be faster
that's the better in my view

Note Function are always better only above the from clause