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

Expand Post »

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

sql Syntax (Toggle Plain Text)
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;
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 )
---------

sql Syntax (Toggle Plain Text)
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 ;
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

sql Syntax (Toggle Plain Text)
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;
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

sql Syntax (Toggle Plain Text)
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;
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;

Last edited by peter_budo; Nov 28th, 2008 at 12:13 pm. Reason: Create table uses sql too, so hence the code tags :-)

hbmarar

Reputation Points: 10

Solved Threads: 0

Junior Poster

Offline

187 posts
since Apr 2005

Permalink

Dec 1st, 2008

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

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

MySQL Syntax (Toggle Plain Text)
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 ;
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 ;

MySQL Syntax (Toggle Plain Text)
DATEDIFF(NOW(), yk_contribution.contribution_date ) <= 365
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

varmadba

Reputation Points: 22

Solved Threads: 9

Junior Poster in Training

Offline

83 posts
since Jun 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: suggest query

Next Thread in MySQL Forum Timeline: Above 2 lakh makes server crash : mysql 5.1, fedora 8

DaniWeb Message

Cancel Changes

User Name
Password