MySQL

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

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Apr 2005

Posts: 183

Reputation: hbmarar is an unknown quantity at this point

Solved Threads: 0

hbmarar

Offline

Junior Poster

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

Nov 21st, 2008

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

 Help with Code Tags 
 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 )
---------

 Help with Code Tags 
 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

 Help with Code Tags 
 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

 Help with Code Tags 
 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 :-)

•

Join Date: Jun 2008

Posts: 79

Reputation: varmadba is an unknown quantity at this point

Solved Threads: 8

varmadba

Offline

Junior Poster in Training

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

Dec 1st, 2008

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

 Help with Code Tags 
 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 ;

 Help with Code Tags 
 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

:- Varma

We are Happy to inform launch of a new site with loads of database related information Site offers wide range of functionality Forums,Blogs,Articles,Editorials and much more
http://www.sqllibrarian.info/